Help w/Dynamic SQL Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help w/Dynamic SQL Statement

Author

Topic

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-04 : 09:28:44

I'm trying to retrieve the value of a variable column into a variable using the EXEC command, note the code below:

DECLARE @nvcColumnName NVARCHAR (30),
@nvcTableName NVARCHAR (30),
@nvcUserID NVARCHAR (9),
@nvcDynamicSQLString NVARCHAR (500)

SET @nvcColumnName = 'UserID'
SET @nvcTableName = 'Users'
SET @nvcUserID = 'XXXXXXXXX'

SET @nvcDynamicSQLString = 'DECLARE @nvcReturnedColumnValue NVARCHAR (30) SELECT @nvcReturnedColumnValue = ' + @nvcColumnName + ' FROM ' + @nvcTableName + ' WHERE UserID = ' + @nvcUserID

EXEC (@nvcDynamicSQLString)

PRINT '@nvcDynamicSQLString = ' + @nvcDynamicSQLString

Query analyzer displays that the command completed successfully, but I don't see the print on the screen.

Any help would be greatly appreciated...

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 10:19:38

Well, it doesn't work that way.

And what happens anyway when you try and select a column with a datatype of anything but char or varchar?

And are you also sure that you will only return 1 row?

Why do you need to do this dynamically.

The way to do this is you need to insert that value in to a permamnet table, the select the value from there.

But then there a whole new set of problems with thta...ya gotta get the spid and insert it so multiple calls of the sproc don't clobber each other....it just ain't woth it...

Brett

8-)

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-04 : 13:14:07

Thanks for the response, but the reason that I need to do this is because I need to be able to determine column values dynamically within a stored procedure based upon a security model. I have a table of applications that each have a unique security model. I have another table that tells me which security model the apps are using. And yet another table of users that have specific demographic information. When I compare the user's demographic information against the app specific security model, only then will I know which group to place the user in and which apps they have access to. It is a pretty simple design and one that can be accomodated by programming in other languages and one that I was hoping could be accomplished directly in T-SQL.

I think I may have found what I was looking for in the way of a temporary table similar to what you describe with a permanent table below, however I still have some more testing. If anyone else has any suggestions I'm still open...

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 14:03:07

A temp table won't work...the table will be in the context of the new spid generated by the execute. You have to use a permanent table..

Let me dig around some...

Brett

8-)

MercuryNewt
Starting Member

12 Posts

Posted - 2005-01-04 : 14:10:34

This works...

DECLARE @nvcColumnName NVARCHAR (30),
@nvcTableName NVARCHAR (30),
@nvcUserID NVARCHAR (9),
@nvcDynamicSQLString NVARCHAR (500),
@nvcDynamicColumnValue NVARCHAR (30)

SET @nvcColumnName = 'FirstName'
SET @nvcTableName = 'Users'
SET @nvcUserID = 'XXXXXXXXX'

SET @nvcDynamicSQLString = 'SELECT ' + @nvcColumnName + ' AS DynamicColumn INTO ##TempForSP FROM ' + @nvcTableName + ' WHERE UserID = ' + @nvcUserID

EXEC (@nvcDynamicSQLString)

SELECT @nvcDynamicColumnValue = DynamicColumn
FROM ##TempForSP

DROP TABLE ##TempForSP

PRINT '@nvcDynamicColumnValue = ' + @nvcDynamicColumnValue

Will I have issues with multiple users accessing the stored procedure that contains this code? Will the global temp table be exposed to other users and therefore the last select statement may produce multiple rows?

Thanks again for your help...

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-04 : 15:31:06

Not really, that's a global temp table, the sproc would fail if another invocation of the sproc was still using it..

This works


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Spid int, Col1 varchar(8000))
GO


DECLARE @sql varchar(8000), @table_name sysname, @column_name1 sysname, @column_name2 sysname, @OrderId int, @SPID int

SELECT @table_name = 'Orders', @column_Name1 = 'CustomerId', @column_name2 = 'OrderId', @OrderId = 10254

SELECT @sql = ' DELETE FROM myTable99 WHERE Spid = ' + CONVERT(varchar(25),@@SPID) + CHAR(13) + CHAR(10) 
	+ ' INSERT INTO myTable99(Spid, Col1) ' + CHAR(13) + CHAR(10)
	+ '      SELECT ' + + CONVERT(varchar(25),@@SPID) + ', ' + @column_name1 + CHAR(13) + CHAR(10) 
	+ '        FROM ' + @table_name + ' WHERE ' + @column_name2 + ' = ' + CONVERT(varchar(25),@OrderId) + CHAR(13) + CHAR(10)

SELECT @sql

EXEC(@sql)  

DECLARE @myValue varchar(10)
SELECT @myValue = Col1 FROM myTable99 WHERE Spid = @@SPID

SELECT @myValue

GO
SET NOCOUNT OFF
DROP TABLE myTable99

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources