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

Transact-SQL (2005)

dynamic SQL

Author

Topic

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 17:23:57

I am trying to call a stored procedure that is stored in a database table. I am using sq_executesql but for some reason cannot get it to work correctly. Does anyone have any suggestions?

declare @string nvarchar(50)
declare @strings nvarchar(max)
declare @employee_in nvarchar(50)
declare @paramdef1 nvarchar(max)
set @string = 'kpi..employeeviews'
set @strings = 'N''exec ' + @string + ''''
set @paramdef1 = 'N''@employee_in nvarchar(50)' + ''''
execute sp_executesql @strings, @paramdef1, @employee_in = 'admin'

Thanks in advance.

The Yak Village Idiot

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 17:29:09

why would you need dynamic SQL for this? Couldnt you exec the proc directly?


EXEC kpi..employeeviews @employee_in

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 17:30:39

Why do you use dynamic sql for this?

exec kpi..employeeviews 'admin'

E 12°55'05.25"
N 56°04'39.16"

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 17:31:33

I wont know what the name of the procedure is. The user defines which module to use and each module has a different stored procedure. So I would need to generate the exec command using dynamic sql.

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 17:33:37

I could use nested if statements but that would get messy. Plus we are building this over time and it would be much simpler to just add the module type and sp then it would be to go back to a long nested if and try to add it there.

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 18:06:48

I have also just tried to execute it directly to ensure that my strings were correct but I am getting a different error now. The code:

declare @employee_in nvarchar(50)
execute sp_executesql
N'Exec kpi..employeeviews', N'@employee_in nvarchar(50)',
@employeeuid_in = 'admin'

This script yeilds the following error message:

Parameterized Query '(@employee_in nvarchar(50))Exec kpi..employeeviews' expects parameter @employee_in, which was not supplied.

Any thoughts?

The Yak Village Idiot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:13:52

[code]DECLARE @NameSP NVARCHAR(200),
@Exec NVARCHAR(300),
@Employee NVARCHAR(50),
@Param NVARCHAR(200)

SELECT @NameSP = 'kpi..employeeviews',
@Exec = N'exec ' + @NameSP,
@Param = N'@Employee NVARCHAR(50)'

EXEC SP_EXECUTESQL @Exec, @Param, @Employee = 'admin'[/code]

E 12°55'05.25"
N 56°04'39.16"

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 18:20:48

How does the syntax change for passing multiple params?

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 18:23:28

Peso, the script returned a similar error message to the one I was getting:

Msg 201, Level 16, State 4, Procedure EmployeeViews, Line 0
Procedure or Function 'EmployeeViews' expects parameter '@Employeeuid_In', which was not supplied.

The Yak Village Idiot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:29:36

[code]DECLARE @NameSP NVARCHAR(200),
@Exec NVARCHAR(300),
@Employee NVARCHAR(50),
@Param NVARCHAR(200)

SELECT @NameSP = 'kpi..employeeviews',
@Exec = N'exec ' + @NameSP,
@Param = N'@Employee_IN NVARCHAR(50)'

EXEC SP_EXECUTESQL @Exec, @Param, @Employee_IN = 'admin'[/code]

E 12°55'05.25"
N 56°04'39.16"

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 18:34:08

For some reason it isnt passing the param to the procedure. The syntax all looks correct:

DECLARE @NameSP NVARCHAR(200),
@Exec NVARCHAR(300),
@Employee NVARCHAR(50),
@Param NVARCHAR(200)

SELECT @NameSP = 'kpi..employeeviews',
@Exec = N'exec ' + @NameSP,
@Param = N'@Employeeuid_IN NVARCHAR(50)'

EXEC SP_EXECUTESQL @Exec, @Param, @Employeeuid_in = 'admin'

The Yak Village Idiot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:38:35

This works for me with my test database on my laptop

DECLARE @NameSP NVARCHAR(200),
@Exec NVARCHAR(300),
@Employee NVARCHAR(50),
@Param NVARCHAR(200)

SELECT @NameSP = 'test..test',
@Exec = N'exec ' + @NameSP,
@Param = N'@a int'

EXEC SP_EXECUTESQL @Exec, @Param, @a = 2

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:39:47

This works too!

DECLARE @NameSP NVARCHAR(200),
@Exec NVARCHAR(300),
@Employee NVARCHAR(50),
@Param NVARCHAR(200)

SELECT @NameSP = 'test..test',
@Exec = N'exec ' + @NameSP,
@Param = N'@b int'

EXEC SP_EXECUTESQL @Exec, @Param, @b = 2

With this, I think you have multiple (more than 1) parameters!
Please check if that's the case.

E 12°55'05.25"
N 56°04'39.16"

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 18:49:10

Yeah this is unusual. We are using the same syntax but for some reason my param is wrong or something

declare @string nvarchar(200),
@exe nvarchar(300),
@employeeuid_in nvarchar(50),
@paramdef1 nvarchar(200)

select @string = 'kpi..employeeviews',
@exe = N'exec ' + @string,
@paramdef1 = N'@employeeuid_in nvarchar(50)'

exec SP_EXECUTESQL @exe, @paramdef1, @employeeuid_in = 'admin'

The Yak Village Idiot

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 18:56:49

Did you check that your SP EmployeeViews needs 1, 2 or more parameters?
When I change my TEST sp to have 2 parameters, I get exactly the same type of error message as you do.

quote:
Msg 201, Level 16, State 4, Procedure Test, Line 0
Procedure or Function 'Test' expects parameter '@b', which was not supplied.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-22 : 19:12:48

Hey! Where did you go?

E 12°55'05.25"
N 56°04'39.16"

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-22 : 19:20:59

quote:
Originally posted by Peso

Hey! Where did you go?

E 12°55'05.25"
N 56°04'39.16"

slow down..Peter...
The OP is not a bot like you

btw, last time I saw you had 9917 posts and now 9947

...

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 19:39:06

sorry i am still here. Yeah the sp has only one param. I am not sure why I cannot get this working. I tried reworking the syntax to no avail.

The Yak Village Idiot

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-22 : 19:44:27

Here is the sp if that is of any help

[dbo].[EmployeeViews](@Employeeuid_In nvarchar(50))

The Yak Village Idiot

mobasha
Starting Member

45 Posts

Posted - 2007-08-23 : 02:07:42

are u calling the stored procedure from other database.

MobashA

jgrant
Yak Posting Veteran

69 Posts

Posted - 2007-08-23 : 15:09:02

No, I am calling it from the same db

The Yak Village Idiot