asked on

Setting parameter value using SQL Query

I have a variable in an sql query "@Datarows" that I need to assign a value to. My issue is that I am creating a one SQL for all type of solution, therefore, I have variable "Staging_Table" that changes from time to time. So, I assign a value to the Staging Table and trying to set the @Datarows value, however, I'm getting an error "@DataRows" must be declared. I have already declared the variable, so I have something wrong with my syntax. I have included the SQL below. Thanks.

Declare @SQL VarChar(Max)
Declare @Staging_Table VarChar(255)
Declare @DataRows BigInt

Set @SQL = 'Set @DataRows = (Select Count(File_ID) From ' + @Staging_Table + ')'
exec (@SQL)

Asim Nazir

Do it like this:

Declare @SQL VarChar(Max)
Declare @Staging_Table VarChar(255)

set @Staging_Table = 'SB_CODEINFO_SIC'
Set @SQL = ' Declare @DataRows BigInt; (Select @DataRows = Count(File_ID) From ' + @Staging_Table + '); print @DataRows '
exec (@SQL)

Asim Nazir

I Removed following line:
set @Staging_Table = 'SB_CODEINFO_SIC'
it should be set in your parameter. I did for testing.

Declare @SQL VarChar(Max)
Declare @Staging_Table VarChar(255)
Set @SQL = ' Declare @DataRows BigInt; (Select @DataRows = Count(File_ID) From ' + @Staging_Table + '); print @DataRows '
exec (@SQL)

ASKER CERTIFIED SOLUTION

VipulKadia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alpesh Patel

Declare it in you dynamic SQL statement

OB1Canobie

ASKER

Your solution worked great.