EXEC sp_executesql does not work

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)

EXEC sp_executesql does not work

Author

Topic

perels
Starting Member

23 Posts

Posted - 2008-05-01 : 20:21:54

Hi everybody,

I have this problem I have been struggling with for more than an hour now, hoping for some help.
Using SQL Server 2005.


DECLARE @IPAddress nvarchar(15)
SET @IPAddress = '127.0.0.1'
DECLARE @UA nvarchar(300) 
SET @UA = 'MSIE'
DECLARE @UserID int 
SET @UserID = 123

EXEC sp_executesql N'User_LogonLog',N'@UserID int, @IPAddress nvarchar(15), @UA nvarchar(300)',@UserID,@IPAddress,@UA

It keeps given me the following error:


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

In my head it should be perfectly valid syntax. I have also read Mr. Sommarskog's nice guide [url]http://www.sommarskog.se/dynamic_sql.html[/url], but no luck either..
I have tried to remove datatypes in parameters declaration, the Unicdode N' and so... nu luck.

If I run the following (without sp_executesql) there is no problem, which indicates the User_LogonLog procedure is work perfectly fine.


EXEC User_LogonLog @UserID,@IPAddress,@UA

What am I missing?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-01 : 20:30:09

Why are you using sp_ExecuteSQL ? Why not just use EXEC? You are not using dynamic SQL, you are just calling a stored procedure and passing parameters, so there is no need for sp_executeSQL.

- Jeff
http://weblogs.sqlteam.com/JeffS

perels
Starting Member

23 Posts

Posted - 2008-05-02 : 03:58:40

Hi Jeff,

Thanks for your answer - I know that I could simply use EXEC.

I was trying to figure out whether this was correct or not.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-02 : 04:27:03

You'd ~~have to~~ could do something like this:

DECLARE @SQL varchar(500)

SET @SQL = 'User_LogonLog ' + @UserID + ', ''' + @IPAdress + ''''

EXEC sp_executesql @SQL

EDIT: ...allthough Jeffs solution below is alot better.

--
Lumbago

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-05-02 : 08:55:38

Lumbago -- Not using parameters eliminates any advantage that sp_executeSQL provides.

You would have to do it like this:

DECLARE @IPAddress nvarchar(15)
SET @IPAddress = '127.0.0.1'
DECLARE @UA nvarchar(300)
SET @UA = 'MSIE'
DECLARE @UserID int
SET @UserID = 123

EXEC sp_executesql N'exec User_LogonLog @UserID,@IPAddress,@UA',N'@UserID int, @IPAddress nvarchar(15), @UA nvarchar(300)',@UserID,@IPAddress,@UA

Again, that really overcomplicates something that is quite simple, so only use sp_ExecuteSQL when you need to, not when directly calling stored procedures.

- Jeff
http://weblogs.sqlteam.com/JeffS

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-02 : 09:11:19

Proves I don't use dynamic sql much I guess...I tend to avoid it whenever I can.

--
Lumbago

perels
Starting Member

23 Posts

Posted - 2008-05-02 : 09:16:44

Hi Jeff and Lumbago,

Thanks for your help - I gotta stick with Jeff's solution :)
- Sure will not begin to overcomplicate things, I was just trying figure out what was wrong with the statement.

Thanks for your help.
Case closed :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources