T-SQL Help

I am getting the following error message, from the stored procedure below it
.
Can someone tell me how to fix this? I am declaring the variable in question
.

Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@recordCount'.

--If passing in a table name, a property type id must also be passed in.
Alter Procedure  UpdateSiteProperty(@
tableToUse varchar(25) = '',
@propertyTypeID Int = 0,
@clientSiteID Int = 0,
@propertyValue varchar(50) = '') As

Declare @sql varchar(200)
Declare @siteID Int
Declare @count Int
Declare @recordCount Int

--Check to see if a user passed in a table name. If not,
--make sure siteId, propertyTypeID, and propertyValue
--have values other than the defaults.
If @tableToUse = ''
Begin
If @clientSiteID > 0 And @propertyTypeID > 0 And @propertyValue <> ''
Begin
Select @siteID = site_id From sites Where client_site_id = @clientSiteID

If @@Rowcount > 0
Begin
Select * From site_properties Where site_id = @siteID And
property_type_id = @propertyTypeID

--If a record is found, update it. Otherwise insert a record.
If @@Rowcount > 0
Begin
Update site_properties Set property_value = @propertyValue
Where site_id = @siteID And property_type_id = @propertyTypeID
Print 'Updated property for site ' + @clientSiteID
End
Else
Begin
Insert Into site_properties (site_id, property_type_id,
property_value)
Values(@siteID, @propertyTypeID, @propertyValue)
Print 'Inserted property for site ' + @clientSiteID
End
End
End
Else
Begin
print 'If you are not using a table as the source, you must supply
values '
+ 'for client_site_id, property_value, and property_type_id'
End
End
Else
Begin
If @propertyTypeID > 0
Begin
Set @sql = 'Select @recordCount = Count(*) From ' + @tableToUse
Execute (@sql)
Set @count = 1

While @count <= @recordCount
Begin
Set @sql = 'Select @clientSiteID = client_site_id, @propertyValue =
property_value From '
+ @tableToUse + 'Where record_id = @count'
Execute (@sql)
/*
Select @clientSiteID = client_site_id, @propertyValue = property_value
From  site_properties_temp

Where record_id = @count
*/
If @@Rowcount > 0
Begin
Select @siteID = site_id From sites Where client_site_id =
@clientSiteID

If @@Rowcount > 0
Begin
Select * From site_properties Where site_id = @siteID And
property_type_id = @propertyTypeID

--If a record is found, update it. Otherwise insert a record.
If @@Rowcount > 0
Begin
Update site_properties Set property_value = @propertyValue
Where site_id = @siteID And property_type_id = @propertyTypeID
Print 'Updated property for site ' + @clientSiteID
Set @sql = 'Update ' + @tableToUse + 'Set updated = 1 Where
client_site_id = @clientSiteID'
Execute (@sql)
End
Else
Begin
Insert Into site_properties (site_id, property_type_id,
property_value)
Values(@siteID, @propertyTypeID, @propertyValue)
Print 'Inserted property for site ' + @clientSiteID
Set @sql = 'Update ' + @tableToUse + 'Set updated = 1 Where
client_site_id = @clientSiteID'
Execute (@sql)
End
End
End

Set @count = @count + 1
End
End
Else
Begin
Print 'You must pass in the property type id if you pass in a table name.'
End
End

Hi,

See the below URL:-

http://www.sommarskog.se/dynamic_sql.html

Thanks
Hari
SQL Server MVP

"Mike Collins" < MikeCollins@discussi
ons.microsoft.com> wrote in message
news:01B0B6D9-D19E-4AC2-B64C- 2062C595D23C@microso
ft.com...
>I am getting the following error message, from the stored procedure below
>it.
> Can someone tell me how to fix this? I am declaring the variable in
> question.
>
> Server: Msg 137, Level 15, State 1, Line 1
> Must declare the variable '@recordCount'.
>
> --If passing in a table name, a property type id must also be passed in.
> Alter Procedure  UpdateSiteProperty(@
tableToUse varchar(25) = '',
> @propertyTypeID Int = 0,
> @clientSiteID Int = 0,
> @propertyValue varchar(50) = '') As
>
> Declare @sql varchar(200)
> Declare @siteID Int
> Declare @count Int
> Declare @recordCount Int
>
> --Check to see if a user passed in a table name. If not,
> --make sure siteId, propertyTypeID, and propertyValue
> --have values other than the defaults.
> If @tableToUse = ''
> Begin
> If @clientSiteID > 0 And @propertyTypeID > 0 And @propertyValue <> ''
> Begin
> Select @siteID = site_id From sites Where client_site_id = @clientSiteID
>
> If @@Rowcount > 0
> Begin
> Select * From site_properties Where site_id = @siteID And
> property_type_id = @propertyTypeID
>
> --If a record is found, update it. Otherwise insert a record.
> If @@Rowcount > 0
> Begin
> Update site_properties Set property_value = @propertyValue
> Where site_id = @siteID And property_type_id = @propertyTypeID
> Print 'Updated property for site ' + @clientSiteID
> End
> Else
> Begin
> Insert Into site_properties (site_id, property_type_id,
> property_value)
> Values(@siteID, @propertyTypeID, @propertyValue)
> Print 'Inserted property for site ' + @clientSiteID
> End
> End
> End
> Else
> Begin
> print 'If you are not using a table as the source, you must supply
> values '
> + 'for client_site_id, property_value, and property_type_id'
> End
> End
> Else
> Begin
> If @propertyTypeID > 0
> Begin
> Set @sql = 'Select @recordCount = Count(*) From ' + @tableToUse
> Execute (@sql)
> Set @count = 1
>
> While @count <= @recordCount
> Begin
> Set @sql = 'Select @clientSiteID = client_site_id, @propertyValue =
> property_value From '
> + @tableToUse + 'Where record_id = @count'
> Execute (@sql)
> /*
> Select @clientSiteID = client_site_id, @propertyValue = property_value
> From  site_properties_temp

> Where record_id = @count
> */
> If @@Rowcount > 0
> Begin
> Select @siteID = site_id From sites Where client_site_id =
> @clientSiteID
>
> If @@Rowcount > 0
> Begin
> Select * From site_properties Where site_id = @siteID And
> property_type_id = @propertyTypeID
>
> --If a record is found, update it. Otherwise insert a record.
> If @@Rowcount > 0
> Begin
> Update site_properties Set property_value = @propertyValue
> Where site_id = @siteID And property_type_id = @propertyTypeID
> Print 'Updated property for site ' + @clientSiteID
> Set @sql = 'Update ' + @tableToUse + 'Set updated = 1 Where
> client_site_id = @clientSiteID'
> Execute (@sql)
> End
> Else
> Begin
> Insert Into site_properties (site_id, property_type_id,
> property_value)
> Values(@siteID, @propertyTypeID, @propertyValue)
> Print 'Inserted property for site ' + @clientSiteID
> Set @sql = 'Update ' + @tableToUse + 'Set updated = 1 Where
> client_site_id = @clientSiteID'
> Execute (@sql)
> End
> End
> End
>
> Set @count = @count + 1
> End
> End
> Else
> Begin
> Print 'You must pass in the property type id if you pass in a table name.'
> End
> End
>
>

Mike,

You have to use sp_executesql to use an output parameter with a dynamic sql.

>	Set @sql = 'Select @recordCount = Count(*) From ' + @tableToUse
> 	Execute (@sql)
> 	Set @count = 1
>
> 	While @count <= @recordCount

set @sql = 'select @recordCount = Count(*) From ' +  quotename(@tableToUs
e)

exec sp_executesql @sql, N'@recordCount int output', @recordCount output
...


AMB

"Mike Collins" wrote:

> I am getting the following error message, from the stored procedure below 
it.
> Can someone tell me how to fix this? I am declaring the variable in questi
on.
>
> Server: Msg 137, Level 15, State 1, Line 1
> Must declare the variable '@recordCount'.
>
> --If passing in a table name, a property type id must also be passed in.
> Alter Procedure  UpdateSiteProperty(@
tableToUse varchar(25) = '',
>  																		@p
ropertyTypeID Int = 0,
>  																		@c
lientSiteID Int = 0,
>  																		@p
ropertyValue varchar(50) = '') As
>
> Declare @sql varchar(200)
> Declare @siteID Int
> Declare @count Int
> Declare @recordCount Int
>
> --Check to see if a user passed in a table name. If not,
> --make sure siteId, propertyTypeID, and propertyValue
> --have values other than the defaults.
> If @tableToUse = ''
> 	Begin
> 		If @clientSiteID > 0 And @propertyTypeID > 0 And @propertyValue <> ''
> 			Begin
> 				Select @siteID = site_id From sites Where client_site_id = @clientSite
ID
>
> 				If @@Rowcount > 0
> 					Begin
> 						Select * From site_properties Where site_id = @siteID And
> property_type_id = @propertyTypeID
>
> 						--If a record is found, update it. Otherwise insert a record.
> 						If @@Rowcount > 0
> 							Begin
> 								Update site_properties Set property_value = @propertyValue
> 								Where site_id = @siteID And property_type_id = @propertyTypeID
> 								Print 'Updated property for site ' + @clientSiteID
> 							End
> 						Else
> 							Begin
> 								Insert Into site_properties (site_id, property_type_id,
> property_value)
>  								Values(@site
ID, @propertyTypeID, @propertyValue)
> 								Print 'Inserted property for site ' + @clientSiteID
> 							End
> 					End
> 			End
> 		Else
> 			Begin
> 				print 'If you are not using a table as the source, you must supply
> values '
> 						+ 'for client_site_id, property_value, and property_type_id'
> 			End
> 	End
> Else
> 	Begin
> 		If @propertyTypeID > 0
> 			Begin
> 				Set @sql = 'Select @recordCount = Count(*) From ' + @tableToUse
> 				Execute (@sql)
> 				Set @count = 1
>
> 				While @count <= @recordCount
> 					Begin
> 						Set @sql = 'Select @clientSiteID = client_site_id, @propertyValue =
> property_value From '
> 												+ @tableToUse + 'Where record_id = @count'
> 						Execute (@sql)
> 						/*
> 						Select @clientSiteID = client_site_id, @propertyValue = property_val
ue
> 						From  site_properties_temp

> 						Where record_id = @count
> 						*/
> 						If @@Rowcount > 0
> 							Begin
> 								Select @siteID = site_id From sites Where client_site_id =
> @clientSiteID
>
> 								If @@Rowcount > 0
> 									Begin
> 										Select * From site_properties Where site_id = @siteID And
> property_type_id = @propertyTypeID
>
> 										--If a record is found, update it. Otherwise insert a record.
> 										If @@Rowcount > 0
> 											Begin
> 												Update site_properties Set property_value = @propertyValue
> 												Where site_id = @siteID And property_type_id = @propertyTypeID
> 												Print 'Updated property for site ' + @clientSiteID
> 												Set @sql = 'Update ' + @tableToUse + 'Set updated = 1 Where
> client_site_id = @clientSiteID'
> 												Execute (@sql)
> 											End
> 										Else
> 											Begin
> 												Insert Into site_properties (site_id, property_type_id,
> property_value)
>  												Values(@
siteID, @propertyTypeID, @propertyValue)
> 												Print 'Inserted property for site ' + @clientSiteID
> 												Set @sql = 'Update ' + @tableToUse + 'Set updated = 1 Where
> client_site_id = @clientSiteID'
> 												Execute (@sql)
> 											End
> 									End
> 							End
>
> 						Set @count = @count + 1
> 					End
> 			End
> 		Else
> 			Begin
> 				Print 'You must pass in the property type id if you pass in a table na
me.'
> 			End
> 	End
>
>

Excellent...thank you very much.

"Alejandro Mesa" wrote:

> Mike,
>
> You have to use sp_executesql to use an output parameter with a dynamic sq
l.
> 
>
> set @sql = 'select @recordCount = Count(*) From ' +  quotename(@tableToUs
e)
>
> exec sp_executesql @sql, N'@recordCount int output', @recordCount output
> ...
>
>
> AMB
>
> "Mike Collins" wrote:
>