How can I build an SQL statement string, depending on parameters passed in, in a stored proc and execute it?

Question

Using SQL Server 2005..

You can see what I am trying to do from the SQL below............

DECLARE

@SQLStr Varchar(1000);

@TestVar Varchar(1)

SET @TestVar = '1'

--Build the sql depending on......

IF @TestVar = '0' THEN

SET @SQLStr = 'Select * from MyTable'

ELSE

SET @SQLStr = 'Select * from MyTable Where MyTable.Id = ' @TestVar

--Execute the SQL

Exec @SQLStr

Answer 1

For dynamic SQL, use the syntax exec(@SQLStr) and you should be fine.

The syntax for creating a stored proc for this would be something like this:

CREATE PROCEDURE dbo.myProc

@TestVar Varchar(1)

AS

DECLARE @SQLStr Varchar(1000)

--Build the sql depending on......

IF @TestVar = '0'

SET @SQLStr = 'Select * from MyTable'

ELSE

SET @SQLStr = 'Select * from MyTable Where MyTable.Id = ' + @TestVar

--Execute the SQL

Exec (@SQLStr)

Go