Dynamic Query Execution Using sp_executeSQL
-
Hi All,
I am trying to Change the Password of users using a Dynamic QUery like this
Execute sp_executesql N'sp_password null,''YC3STLXO32'',@usr_id'
I am getting an error while executing the Query saying "Must declare the variable '@usr_id'."
Can aynone help me in this regards
-
First of all, why are you doing this with dynamic sql??
Anyway, here is how to do it:
Execute sp_executesql N'sp_password null,''YC3STLXO32'', ' + @usr_idIf @usr_id is a character type you need to add quotes:
Execute sp_executesql N'sp_password null,''YC3STLXO32'', ''' + @usr_id + ''''However, note that by doing this with dynamic sql you are opening up for sql injection attacks. Like I said first, why do you need dynamic sql for this?
-
Hi Chris,
Thanks for your reply
but when i tried executing this SP with the syantax mentioned by you. I am getting an error saying incorrect syntax near +
CREATE proc sp_cp
(
@usr_id varchar(50)
)
as
begin
Execute sp_executesql N'sp_password null,''YC3STLXO32'', ''' + @usr_id + ''''
end
Actually speaking i am not using Dyanmic Queries my question was how to substitute parameter values in sp_executeSql as "execute" does not support parameter substitution. Am i right with my understanding
-
But why do you not just do this then:
CREATE proc sp_cp
(
@usr_id varchar(50)
)
as
begin
EXECUTE sp_password null, 'YC3STLXO32' , @usr_id
end
-
HI Chris,
Thanks a lot.. I didn't know that we can do that by the way u've told.. i've made it very complex..
Anyways thanks a lot chris.. and sorry for wasting your time
-
Hi Guys,
I think you will find the reason for the dynamic sql error was that @usr_id is numeric. You will need to CAST / CONVERT it before adding it to your string e.g:
Execute sp_executesql N'sp_password null,''YC3STLXO32'', ''' + CAST(@usr_id as NVARCHAR(10))+ ''''
You definately dont need the complexities of Dynamic SQL to accomplish this task......
but EXEC does quite happily support parameter substituion. E.g.DECLARE @SQLSelectFrom NVARCHAR(4000)
DECLARE @SQLWhere NVARCHAR(4000)
DECLARE @InputVariable NVARCHAR(100)
SET @InputVariable = 'Roger'
SET @SQLSelectFrom = N'SELECT * FROM A_TABLE'
SET @SQLWhere = N'WHERE A_COLUMN_VALUE = ' + @InputVariable
PRINT (@SQLSelectFrom + @SQLWhere)
--EXEC (@SQLSelectFrom + @SQLWhere)
Change the sql to something relevant to the DB you are working in and un-comment the exec......
Have Fun
Steve
We need men who can dream of things that never were.
-
Hey, no waste. As long as something was learned it is never a waste.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply