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
 SQL Server Development (2000)
 how to use use sp_executesql

Author  Topic 

vl
Starting Member

14 Posts
Posted - 2007-09-28 : 18:34:41
I try to use sp_executesql dynamically call different stored procedure, the stored procedure could have different type of parameter.

When I execute the following code I get the following error message:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


declare @cmd varchar(2000)
declare @StoreProcedureName varchar(50)
declare @ParameterName varchar(50)
declare @ParameterValue varchar(50)
declare @ParameterList varchar(500)

set @ParameterName = 'CreatedOn' -- it is datetime parameter
set @ParameterValue = '05/27/2005'
set @ParameterList = ' @' + @ParameterName + '=convert(datetime, ''' + @ParameterValue + ''')'

set @StoreProcedureName = 'exec test.dbo.Test_MemberGet '
set @cmd = 'exec ' + @StoreProcedureName + @ParameterList

exec sp_executesql @cmd

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-28 : 18:38:16
Do a print @Cmd and see how your SQL is building up. 


declare @cmd varchar(2000)
declare @StoreProcedureName varchar(50)
declare @ParameterName varchar(50)
declare @ParameterValue varchar(50)
declare @ParameterList varchar(500)

set @ParameterName = 'CreatedOn' -- it is datetime parameter
set @ParameterValue = '05/27/2005'
set @ParameterList = ' @' + @ParameterName + '=convert(datetime, ''' + @ParameterValue + ''')'

set @StoreProcedureName = 'exec test.dbo.Test_MemberGet '
set @cmd = 'exec ' + @StoreProcedureName + @ParameterList 

--exec sp_executesql @cmd

Print @cmd



Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-09-28 : 18:45:07
You need to change @cmd to nvarchar as the error suggests.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

vl
Starting Member

14 Posts
Posted - 2007-09-28 : 19:04:55
Ok. I changed the @cmd to nvarchar(2000). Now I got different error.

The print out of the @cmd is
exec Test.dbo.Test_MemberGet @CreatedOn=convert(datetime, '05/27/2005')

The error I got is :
Incorrect syntax near the keyword 'convert' this time.
If will be fine, if I do this:
declare @CreatedOn datetime
set @CreatedOn = convert(datetime, '05/27/2005')
exec Test.dbo.Test_MemberGet @CreatedOn=@CreatedOn

But it will fail if I pass the value directly:
exec Test.dbo.Test_MemberGet @CreatedOn=convert(datetime, '05/27/2005')

declare @cmd nvarchar(2000)
declare @StoreProcedureName varchar(50)
declare @ParameterName varchar(50)
declare @ParameterValue varchar(50)
declare @ParameterList varchar(500)

set @ParameterName = 'CreatedOn' -- it is datetime parameter
set @ParameterValue = '05/27/2005'
set @ParameterList = ' @' + @ParameterName + '=convert(datetime, ''' + @ParameterValue + ''')'

set @StoreProcedureName = 'Test.dbo.Test_MemberGet '
set @cmd = 'exec ' + @StoreProcedureName + @ParameterList

print @cmd

exec sp_executesql @cmd
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-09-28 : 19:08:17
check out Example B from books on line when you look for sp_ExecuteSql.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

vl
Starting Member

14 Posts
Posted - 2007-09-28 : 19:16:10
Now the problem become simple. It matters how to call stored procedure.

I execute the following command it works form Query Analyzer:
--------------------------------------------------------------
declare @CreatedOn datetime
set @CreatedOn = convert(datetime, '05/27/2005')
exec Test.dbo.Test_MemberGet @CreatedOn=@CreatedOn

But if I execute the following statement from query analyzer put constant value I fail:
-----------------------------------------------------------------------------------------
exec Test.dbo.Test_MemberGet @CreatedOn=convert(datetime, '05/27/2005')

The error is:
Incorrect syntax near the keyword 'convert'.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2007-09-29 : 04:39:56
1. You can't perform a function in a parameter.
2. You don't have to convert to datetime. It will be done automatically. Just specify your date in ISO format YYYYMMDD

exec Test.dbo.Test_MemberGet @CreatedOn = '20050527'


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-09-29 : 05:06:41
Why not use the full functionality of sp_ExecuteSQL?

EXEC dbo.sp_ExecuteSQL
    N'test.dbo.Test_MemberGet',
    '@CreatedOn datetime', -- or even VARCHAR(10) would probably be OK, SQL will implicitly convert
    '20050527'

you can supply parameters that are not required by the SQL Statement, which can be helpful in having generic syntax for sp_ExecuteSQL

Kristen
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-10-01 : 13:44:28
quote:
Originally posted by Kristen

Why not use the full functionality of sp_ExecuteSQL?

EXEC dbo.sp_ExecuteSQL
    N'test.dbo.Test_MemberGet',
    '@CreatedOn datetime', -- or even VARCHAR(10) would probably be OK, SQL will implicitly convert
    '20050527'

you can supply parameters that are not required by the SQL Statement, which can be helpful in having generic syntax for sp_ExecuteSQL

Kristen


I run this SQL statement (after making a database as 'test', and creating and compiling the revelant stored procedure) and I got the following error (near database name 'test'). So, what is wrong with that?

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'test'.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-10-01 : 13:49:53
You probably need EXEC Test.dbo.sp_ExecuteSQL N'dbo.Test_MemberGet'...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-10-01 : 13:54:26
quote:
Originally posted by vl

Ok. I changed the @cmd to nvarchar(2000). Now I got different error.

The print out of the @cmd is
exec Test.dbo.Test_MemberGet @CreatedOn=convert(datetime, '05/27/2005')

The error I got is :
Incorrect syntax near the keyword 'convert' this time.
If will be fine, if I do this:
declare @CreatedOn datetime
set @CreatedOn = convert(datetime, '05/27/2005')
exec Test.dbo.Test_MemberGet @CreatedOn=@CreatedOn

But it will fail if I pass the value directly:
exec Test.dbo.Test_MemberGet @CreatedOn=convert(datetime, '05/27/2005')

declare @cmd nvarchar(2000)
declare @StoreProcedureName varchar(50)
declare @ParameterName varchar(50)
declare @ParameterValue varchar(50)
declare @ParameterList varchar(500)

set @ParameterName = 'CreatedOn' -- it is datetime parameter
set @ParameterValue = '05/27/2005'
set @ParameterList = ' @' + @ParameterName + '=convert(datetime, ''' + @ParameterValue + ''')'

set @StoreProcedureName = 'Test.dbo.Test_MemberGet '
set @cmd = 'exec ' + @StoreProcedureName + @ParameterList

print @cmd

exec sp_executesql @cmd


Try the following. it should work.

declare @cmd nvarchar(2000)
declare @StoreProcedureName varchar(50)
declare @ParameterName varchar(50)
declare @ParameterValue varchar(50)
declare @ParameterList varchar(500)


set @ParameterName = 'CreateOn='
set @ParameterValue = '07/04/1996'
set @ParameterList = ' @' + @ParameterName + '''' + @ParameterValue + '''' -- you are expected to follow the syntax here 4 single quotes for starting and closing string.

set @StoreProcedureName = ' Test.dbo.Test_MemberGet '
set @cmd = 'exec ' + @StoreProcedureName + @ParameterList


exec sp_executesql @cmd
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-10-01 : 13:59:58
quote:
Originally posted by tkizer

You probably need EXEC Test.dbo.sp_ExecuteSQL N'dbo.Test_MemberGet'...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/


That does not work, too. The same error as my previous post "... Incorrect syntax near test. .....'
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-01 : 14:38:00
That suggests that Test_MemberGet does not exist as an Sproc

Try:

test.dbo.sp_help 'dbo.Test_MemberGet'

to see if the Sproc is actually there.

Kristen
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-10-01 : 15:34:04
quote:
Originally posted by Kristen

That suggests that Test_MemberGet does not exist as an Sproc

Try:

test.dbo.sp_help 'dbo.Test_MemberGet'

to see if the Sproc is actually there.

Kristen


Kristen,
For testing on my QA. I use MS NorthWind DB and create another stored procedure as 'dbo.upaagGetOrdersByOrderDate' on table [Orders]. The stored procedure is run well in separatly in a QA window instance.

Here the simple SP: (made in existing NorthWind db)

--------------
CREATE PROCEDURE dbo.upaagGetOrdersByOrderDate
@OrderDate datetime
AS
SET NOCOUNT ON

select * from [Orders] where orderDate=@OrderDate

------------------
I tried to follow your suggested SQL statement, and I got the error "... in correct syntax near dbo..."

declare @odp datetime
set @odp = '07/04/1996'

exec sp_executesql
N'dbo.upaagGetOrdersByOrderDate', '@OrderDate datetime', @odp
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-01 : 15:43:59
You are not wrong!

This would work a WHOLE lot better

declare @odp datetime
set @odp = '07/04/1996'

exec sp_executesql
N'EXEC dbo.upaagGetOrdersByOrderDate', N'@OrderDate datetime', @odp

and the earlier errant code should probably be:

EXEC dbo.sp_ExecuteSQL
    N'EXEC test.dbo.Test_MemberGet @CreatedOn=@CreatedOn',
    N'@CreatedOn datetime', -- or even VARCHAR(10) would probably be OK, SQL will implicitly convert
    '20050527'

Kristen
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-10-01 : 16:15:02
quote:
Originally posted by Kristen

You are not wrong!

This would work a WHOLE lot better

declare @odp datetime
set @odp = '07/04/1996'

exec sp_executesql
N'EXEC dbo.upaagGetOrdersByOrderDate', N'@OrderDate datetime', @odp

and the earlier errant code should probably be:

EXEC dbo.sp_ExecuteSQL
    N'EXEC test.dbo.Test_MemberGet @CreatedOn=@CreatedOn',
    N'@CreatedOn datetime', -- or even VARCHAR(10) would probably be OK, SQL will implicitly convert
    '20050527'

Kristen


Well,
I am not lucky when I tried to run the following I get another error:
declare @dp datetime
set @dp = '07/04/1996'

exec sp_executesql N'EXEC upaagGetOrdersByOrderDate', N'@OrderDate datetime', @dp

-- error
Server: Msg 201, Level 16, State 3, Procedure upaagGetOrdersByOrderDate, Line 0
Procedure 'upaagGetOrdersByOrderDate' expects parameter '@OrderDate', which was not supplied.
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-01 : 17:07:02
Bizarre, I must have mucked it up in the Cut & paste, sorry about that. I changed the name of the Sproc in testing, so I could more easily delete it later, so assuming that I have done my Find&Replace correctly:

exec sp_executesql N'exec dbo.upaagGetOrdersByOrderDate @OrderDate=@OrderDate', N'@OrderDate datetime', @odp

but I'm starting to wonder whether for a scenario like this if sp_ExecuteSQL has any benefit (i.e. any positive impact on the Query Plan) over just EXEC MySProc ...

Kristen
Go to Top of Page

johnsql
Posting Yak Master

161 Posts
Posted - 2007-10-02 : 09:02:18
quote:
Originally posted by Kristen

Bizarre, I must have mucked it up in the Cut & paste, sorry about that. I changed the name of the Sproc in testing, so I could more easily delete it later, so assuming that I have done my Find&Replace correctly:

exec sp_executesql N'exec dbo.upaagGetOrdersByOrderDate @OrderDate=@OrderDate', N'@OrderDate datetime', @odp

but I'm starting to wonder whether for a scenario like this if sp_ExecuteSQL has any benefit (i.e. any positive impact on the Query Plan) over just EXEC MySProc ...

Kristen


Yes, I agree and wonder about the uselessness of sp_executesql in dynamic SQL for this case. It turns out that dynamic SQL's role fades out for stored procedures. If you look back one of my previous posts, you can see there is a solution for the OP and all string concatenated strings ties to dynamic SQL. I am afraid that if we use SQL dymamic directly, then the syntax issue drives the dynamic SQL becomes more complicated or simply come back to single, normal call like EXEC stored-procedure-name parameter-names-values !
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2007-10-02 : 09:48:36
Yup, different story if it was for dynamic SQL like:

EXEC sp_ExecuteSQL 
    N'SELECT * FROM MyTable WHERE MyCol = @Param', 
    N'@Param varchar(10)', 
    'ABC123'

Kristen
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-10-02 : 10:06:05
I also agree with the last few posts, I have no idea why sp_executeSQL is being used here ....

This is probably yet another case where the OP should step back and explain in more detail WHAT he is she is trying to do, as opposed to focusing on HOW to do what they think they need to do ...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-10-02 : 10:15:56
I hesitate to give a solution based on what I just wrote, but you have to remember that sp_ExecuteSQL expects a sql statement, not a stored proc name, as indicated. And you cannot name parameters with @ because sp_ExecuteSQL expects that everything with a @ is a param that it should be replacing.

So you need to specify parameters by position, not by name:



create proc TestIt @param int
as
select @param as value

go

exec sp_executeSQL N'exec TestIt @param', N'@param int',@param=1

value       
----------- 
1

(1 row(s) affected)



Note that the @param used by sp_executeSQL is NOT the same as the @param specified in the stored proc name; you could use @p or anything else in sp_ExecuteSQL and it will work.

Again, though, I really do not recommend doing this .. it makes no sense ...

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site