MS SQL Server - T-SQL Help

Author

T-SQL Help

Mike Collins

2005-09-29, 8:23 pm

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

Hari Prasad

2005-09-29, 8:23 pm

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
>
>

Alejandro Mesa

2005-09-29, 8:23 pm

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 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) = '',
> @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 = @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(@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_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 Collins

2005-09-30, 3:23 am

Excellent...thank you very much.

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

Sponsored Links