Setting parameter value using SQL Query

OB1Canobie
OB1Canobie used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Asim NazirProject Manager

Commented:
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 NazirProject Manager

Commented:
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)
It will help you :

      declare @str nvarchar(1000)
      declare @val numeric
      declare @valOUT numeric
      DECLARE @ParmDefinition NVARCHAR(500)

      set @val = 0
      set @str = ''
      set @str = 'select @val = count(*) from sysobjects'
      SET @ParmDefinition = N'@val numeric OUTPUT'

      EXECUTE sp_executesql @str, @ParmDefinition,  @val OUTPUT

      print @str
      --exec(@str)
      print 'VALUE'
      print @val
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)

Commented:
Declare it in you dynamic SQL statement

Author

Commented:
Your solution worked great.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial