Dynamic SQL 1

[drallabs]

The following dynamic sql statement executes the first print statement when the @standid variable is not null. Any ideas, perhaps a dynamic sql qwerk?

declare @standid varchar(100)
Declare @SQL VarChar(1000)

SELECT @SQL = ' SELECT' + @standid + '= standid FROM ' + @TableName + ' where standid is null'
If @standid is null
Begin
print 'ERROR. There are missing standids in the standedits Layer. '
print''
End
Else
print 'NO ERRORS. There arent any Missing Standids.'
print''

 

[mrdenny]

You can't set a variable like this using dynamic SQL.

You need to create a temp table in the calling code, and have the dynamic SQL insert into the temp table. Then get the value from the temp table and do your logic.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[drallabs]

Thanks for your response. I dont understand exactly what you mean, an example would help. I should also mention that the statement exists within a SP.

CREATE Procedure StandEdit_ErrorCheck
@TableName VarChar(100)
AS
--check for missing standid values
declare @standid varchar(100)
Declare @SQL VarChar(1000)

SELECT @SQL = ' SELECT' + @standid + '= standid FROM ' + @TableName + ' where standid is null'
If @standid is null
Begin
print 'ERROR. There are missing standids in the standedits Layer. '
print''
End
Else
print 'NO ERRORS. There arent any Missing Standids.'
print''
Exec( @SQL )
GO

On another note can you recommend a book that covers Dynamic SQL? The on-line help doesnt cut it.

 

[SQLDenis]

you can use sp_executesql with output parameters or exec with a temp table

here is an example of both

USE pubs
GO

--sp_executesql
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)

SELECT @chvTableName = 'Authors'
SELECT @chvSQL = N'SELECT @intTableCount = COUNT(*) FROM ' + @chvTableName

EXEC sp_executesql @chvSQL, N'@intTableCount INT OUTPUT', @intTableCount OUTPUT

SELECT @intTableCount
GO


--EXEC (SQL)
DECLARE @chvTableName VARCHAR(100),
@intTableCount INT,
@chvSQL NVARCHAR(100)


CREATE TABLE #temp (Totalcount INT)
SELECT @chvTableName = 'Authors'
SELECT @chvSQL = 'Insert into #temp Select Count(*) from ' + @chvTableName

EXEC( @chvSQL)

SELECT @intTableCount = Totalcount from #temp

SELECT @intTableCount

DROP TABLE #temp

a really good page about dynamic SQl is here

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

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[drallabs]

Thanks for your response SQLDenis, I dont entirely understand all the syntax, specifically what is the purpose of N'?