activating dynamic sql whereby sp_executesql

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 2005 Forums

Transact-SQL (2005)

activating dynamic sql whereby sp_executesql

Author

Topic

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-02 : 05:33:52

Hi everyone,
The following code to run dynamic sql:


DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:


Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 05:35:34

quote:
Originally posted by pssheba

Hi everyone,
The following code to run dynamic sql:

DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP (@portion) * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

Try putting braces around the variable after Top. Also make usre you're running this on sql 2005 with compatibility level 90

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 05:39:44

try
SELECT @mydynamic = ' SELECT TOP (@portion) * FROM server.databse.dbo.table'

or
SELECT @mydynamic = ' SELECT TOP ' + convert(varchar(20),@portion) + ' * FROM server.databse.dbo.table'
EXEC sp_executesql @mydynamic

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-02 : 06:26:43

quote:
Originally posted by pssheba

Hi everyone,
The following code to run dynamic sql:

DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

Why are you using dynamic sql?

SELECT TOP (@portion) * FROM server.databse.dbo.table

Madhivanan

Failing to plan is Planning to fail

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-02 : 07:58:27

quote:
Originally posted by visakh16

quote:
Originally posted by pssheba

Hi everyone,
The following code to run dynamic sql:

DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP (@portion) * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

Try putting braces around the variable after Top. Also make usre you're running this on sql 2005 with compatibility level 90

Thank, it worked although i dont understand the meaning of those brackets

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-02 : 08:01:56

quote:
Originally posted by madhivanan

quote:
Originally posted by pssheba

Hi everyone,
The following code to run dynamic sql:

DECLARE @params NVARCHAR(4000)
DECLARE @portion INT
SET @portion=6
DECLARE @mydynamic NVARCHAR(4000)
SELECT @mydynamic = ' SELECT TOP @portion * FROM server.databse.dbo.table'
SELECT @params = N'@portion INT '
EXEC sp_executesql @mydynamic,@params, @portion

yields the following error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '@portion'.

anyone has any idea why ? How it should be corrected?
Thanks

Why are you using dynamic sql?

SELECT TOP (@portion) * FROM server.databse.dbo.table

Madhivanan

Failing to plan is Planning to fail

Because @portion's value is not known, as its' name applys: it carries the number of users sent within a prameter to the sp, deviding the number of rows in a table.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-02 : 08:02:52

quote:
Originally posted by nr

try
SELECT @mydynamic = ' SELECT TOP (@portion) * FROM server.databse.dbo.table'

or
SELECT @mydynamic = ' SELECT TOP ' + convert(varchar(20),@portion) + ' * FROM server.databse.dbo.table'
EXEC sp_executesql @mydynamic

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks a lot

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-02 : 08:10:32

If you are just using dynamic sql for @portion then it's not necessary.
SELECT TOP (@portion) * FROM server.databse.dbo.table

should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-08 : 05:39:01

quote:
Originally posted by nr

If you are just using dynamic sql for @portion then it's not necessary.
SELECT TOP (@portion) * FROM server.databse.dbo.table

should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi nr
My dynamic sql is consisted of all kinds of parameters such as @tablename which i had to separate from the entire string and portion which required the brackets. It's a whole "know how" the Dynamic sql but ithink i get to know how to work with sp_executesql.
Thanks a lot to you and all those who gave me a shoulder.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-08 : 05:49:40

quote:
Originally posted by pssheba

quote:
Originally posted by nr

If you are just using dynamic sql for @portion then it's not necessary.
SELECT TOP (@portion) * FROM server.databse.dbo.table

should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi nr
My dynamic sql is consisted of all kinds of parameters such as @tablename which i had to separate from the entire string and portion which required the brackets. It's a whole "know how" the Dynamic sql but ithink i get to know how to work with sp_executesql.
Thanks a lot to you and all those who gave me a shoulder.

Also make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 06:00:41

quote:

Hi nr
My dynamic sql is consisted of all kinds of parameters such as @tablename which i had to separate from the entire string and portion which required the brackets. It's a whole "know how" the Dynamic sql but ithink i get to know how to work with sp_executesql.
Thanks a lot to you and all those who gave me a shoulder.

Had a feeling that was the case which is why I didn't mention it in the first post but you didn't respond when madhivanan suggested it so I thought it worth repeating just in case.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pssheba
Yak Posting Veteran

95 Posts

Posted - 2008-04-08 : 06:44:40

quote:
Originally posted by madhivanan

quote:
Originally posted by pssheba

quote:
Originally posted by nr

If you are just using dynamic sql for @portion then it's not necessary.
SELECT TOP (@portion) * FROM server.databse.dbo.table

should work.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Hi nr
My dynamic sql is consisted of all kinds of parameters such as @tablename which i had to separate from the entire string and portion which required the brackets. It's a whole "know how" the Dynamic sql but ithink i get to know how to work with sp_executesql.
Thanks a lot to you and all those who gave me a shoulder.

Also make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Thanks Madhivanan
For the link and advises.
I'll read it. Maybe that will turn DynamicSql to less intimidating or "Turn to blessing from curse.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources