Dynamic Query need Help - 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

General SQL Server Forums

New to SQL Server Programming

Dynamic Query need Help

Author

Topic

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-06-22 : 11:35:16

declare @tsql varchar(max)
set @tsql = ''
Select @tsql = @tsql + table_spec from meta..[abcd_data_dic_tbl]
Print @tsql

Exec meta..um_drp_table @table_name
EXECUTE ('CREATE TABLE ' + @table_name + ' (' + @nsql + ')')

table_spec field has specification for each fields and there is one record for each field in that table.
This works fine, but what I need to do is for table meta..[abcd_data_dic_tbl], I need to use variable like
@data_dic_tbl so it can be run with other tables as well..

I tried using that in dynamic sql it failed.... need to check with you guys the quick way to do so...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 11:43:12

then you need a dynamic sql for that as well. use sp_executesql to execute first query


DECLARE @SqlString nvarchar(8000)

SET @SqlString='Select @tsql = @tsql + table_spec from ' + @data_dic_tbl

EXEC sp_executesql @SqlString,...

see books online for complete syntax of sp_executesql

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 11:58:11

Also.

*definitely* read this: http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-06-22 : 11:58:35

That doesnt work eventhough I had declared the varaible @tsql on top of the proc, it still says scalar variable @tsql need to be declared.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 12:03:54

have you declare @tsql inside sp_executesql? see how to use sp_executesql

http://msdn.microsoft.com/en-us/library/ms188001.aspx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 12:04:23

The @tsql variable doesn't exist in the scope of the dynamic block.

Try passing it in. (I guess you also want it back out again?)

Example:


SET @SqlString='Select @tsql = @tsql + table_spec from ' + @data_dic_tbl

EXEC sp_executeSql @sql, N'@tsql NVARCHAR(4000) OUTPUT', @tsql OUTPUT

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 12:10:11

Also,

A NVARCHAR can't be 8000 characters long

it can at most be 4000 characters long OR in sql server 2005 and up it can be declared AS NVARCHAR(MAX) which is 2GB long

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-06-22 : 12:32:18

DECLARE @SqlString nvarchar(max)
--DECLARE @tsql nvarchar(max)

SET @SqlString='Select @tsql = @tsql + table_spec from ' + @data_dic_tbl

EXEC sp_executeSql @SqlString, N'@tsql NVARCHAR(MAX) OUTPUT', @tsql OUTPUT

If we run this it throws error saying @tsql need to be declared... not sure what I am doing wrong.. but I am close .. thanks guys

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 12:48:08

quote:
Originally posted by GhantaBro

DECLARE @SqlString nvarchar(max)
DECLARE @tsql nvarchar(max)

SET @SqlString='Select @tsql = @tsql + table_spec from ' + @data_dic_tbl

EXEC sp_executeSql @SqlString, N'@tsql NVARCHAR(MAX) OUTPUT', @tsql=@tsql OUTPUT
SELECT @tsql

If we run this it throws error saying @tsql need to be declared... not sure what I am doing wrong.. but I am close .. thanks guys

uncomment declare and modify as above and try

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-22 : 14:16:17

You need to declare the variable before using it.

However because you are appending to it like this


Select @tsql = @tsql + table_spec from ' + @data_dic_tbl

THen the result is going to be NULL (as you haven't given @tsql a value before using it.) Null + anything is null
I think the *complete* code is


DECLARE @data_dic_tbl NVARCHAR(255)
DECLARE @SqlString nvarchar(max)
DECLARE @tsql nvarchar(max)

SET @tsql = N''
SET @data_dic_tbl = N'myTable'
SET @SqlString='Select @tsql = @tsql + table_spec from [' + @data_dic_tbl + ']'

EXEC sp_executeSql @SqlString, N'@tsql NVARCHAR(MAX) OUTPUT', @tsql=@tsql OUTPUT
SELECT @tsql

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GhantaBro
Posting Yak Master

215 Posts

Posted - 2009-06-22 : 15:32:38

Thanks guys it worked....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources