Need help with a dynamic sql stored procedure

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jack - 21 Jul 2008 21:58 GMT

Hi,
This is the final part of my previous post in the morning. I have changed
the stored procedure quite a bit. The idea is the generate a select
statement where the database needs to be chosen dynamically based on a
parameter 'site' in the procedure. The procedure looks like the following
which compiles fine:

create procedure customer_process_now

@custid varchar(50),
@site varchar(10)

as

declare @sitedbname varchar(25)
declare @sql varchar(4000)
set @sitedbname = 'ts2' + @site + '_app'

select @sql = ' select custid, sales_ytd, sales_lstyr
' + ' from @sitedbname ' + '..tblcustomer '

select @sql = @sql + ' where custid = @custid '

exec sp_executesql @custid, @site, @sql

Now if I execute the above stored procedure as the following:
customer_process_now 'c000001', 'aldkm'

I get the error as follows:

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 18
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

I am not certain why the above error is showing up. Any help is appreciated
in advance.

Reply to this Message

Plamen@sqlstudio.com - 21 Jul 2008 22:31 GMT

A good start will be to read Erland Sommarskog's article on dynamic
SQL:
http://www.sommarskog.se/dynamic_sql.html

The problems you have:

1). Look up the syntax of sp_executesql in Books Online. The first
parameter is the SQL statement. Unless you explicitly name the
parameters you have to provide them in the correct order.

2). You cannot send the database name as parameter. You have to
concatenate it in the SQL:
' + ' from ' + @sitedbname + '..tblcustomer '

Even better, use QUOTENAME: ' + ' from ' + QUOTENAME(@sitedbname) +
'..tblcustomer '

3). Going back to 1, you have to send the correct parameters to
sp_executesql
- first declare @sql as NVARCHAR not VARCHAR
- use: EXEC sp_executesql @sql, N'@custid INT', @custid = @custid

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Jack - 22 Jul 2008 15:28 GMT

Thank you so much Erland and Plamen to help me with explaining the problems
step by step. I have got the procedure changed accordingly. Now I am running
the stored procedure by passing proper parameter. However instead of running
it is giving me errors. Any help is appreciated. Thanks again.

create procedure customer_process_now

@custid Nvarchar(50),
@site Nvarchar(10)

as

declare @sitedbname Nvarchar(25)
declare @sql Nvarchar(4000)
DECLARE @params NVARCHAR(100);
set @sitedbname = 'ts2' + @site + '_app'

select @sql = ' select custid, sales_ytd, sales_lstyr
' + 'from ' + @sitedbname + '..tblcustomer '
select @sql = @sql + ' where custid = @custid '

EXEC sp_executesql @sql, @params, @custid = @custid;

customer_process_now 'c000001', 'aldkm'

Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@custid'.

> A good start will be to read Erland Sommarskog's article on dynamic
> SQL:
[quoted text clipped - 22 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 22 Jul 2008 15:40 GMT

You did not set @params to the proper parameter string. Add the following
line before EXEC:

SET @params = N'@custid NVARCHAR(50)';

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Jack - 22 Jul 2008 16:36 GMT

Thanks Plamen. After changing the error went away with a new error. Could you
please tell me why this error is coming up. Thanks

create procedure customer_process_now

@custid Nvarchar(50),
@site Nvarchar(10)

as

declare @sitedbname Nvarchar(25)
declare @sql Nvarchar(4000)
DECLARE @params NVARCHAR(100);
set @sitedbname = 'ts2' + @site + '_app'
SET @params = N'@custid NVARCHAR(50)';

select @sql = ' select custid, sales_ytd, sales_lstyr
' + 'from ' + @sitedbname + '..tblcustomer '
select @sql = @sql + ' where custid = @custid '
--exec sp_executesql @sql, N'custid Nvarchar(50), N'site Nvarchar(10),
--@custid = @custid, @site = @site
EXEC sp_executesql @sql, @params, @custid = @custid;

select * from tblcustomer
c000001

customer_process_now 'c000001', 'aldkm'

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'ts2aldkm_app..tblcustomer'.

> You did not set @params to the proper parameter string. Add the following
> line before EXEC:
[quoted text clipped - 5 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 22 Jul 2008 16:59 GMT

Your code generates the following query:

select custid, sales_ytd, sales_lstyr
from ts2aldkm_app..tblcustomer where custid = 'c000001'

Run it manually and see if it works. The error indicates this is invalid
database or table, so you have to check if they really exist on your server.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Jack - 22 Jul 2008 17:48 GMT

Thanks a lot for sending me the query output Plamen. I found out the database
name was incorrect. After correcting that everything works fine now. Could
you show me the print statment that gives the generation of sql. Can the
print be used to generate the sql out of stored procedure also. Regards.

> Your code generates the following query:
>
[quoted text clipped - 8 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 22 Jul 2008 18:01 GMT

I simply replaced the EXEC statement with PRINT and added the correct
parameter value:

PRINT @sql

I am not sure I understand what you mean to generate SQL out of the stored
procedure. You can return the @sql variable as output parameter if needed.
Or you can use SELECT @sql, not sure what you needs are.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

Jack - 22 Jul 2008 18:15 GMT

Thanks Plamen. That was very helpful.

> I simply replaced the EXEC statement with PRINT and added the correct
> parameter value:
[quoted text clipped - 9 lines]
> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message