January 15, 2016 at 1:49 am
Hi all,
Im struggling with a problem here. I have an app with a lot of screens based on an sql database using a lot of stored procedures. The app sends '%' as parameter' values where is not a specifically value needed. I have to change this behaviour. The new version of the app will send '' (emty string) insetad of '%'.
I have to found a way to change ALL stored procedures, to dinamically find out char or varchar type parameters and do something like this:
set @param = '%'+@param+'%'
Till now, I have tried to insert thios fragment at the beginning of the procedure
CREATE TABLE #set_exec (strSQL varchar(255) null)
INSERT INTO #set_exec
select strSQL =' set ' + nume_parametru + '= ' + ''''+'%'+'''' +' +'+ nume_parametru+ '+' +''''+'%'+'''' from [dbo].[ListaParametriChar] (OBJECT_NAME(@@PROCID))
DECLARE ParamList CURSOR FOR
SELECT * from #set_exec
OPEN ParamList
FETCH NEXT FROM ParamList INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN
--select @strSQL
EXEC (@strSQL)
FETCH NEXT FROM ParamList into @strSQL
END
CLOSE ParamList
DEALLOCATE ParamList
But it's not working, as the scope of the variable is inside the batch EXEC (@strSQL) and is not passed to outer variable, which is my real parameter.
DO you have any idea how to solve this problem with other approach?
Thanks in advance for any sugestion.
January 15, 2016 at 2:18 am
simone.hagiu (1/15/2016)
Hi all,Im struggling with a problem here. I have an app with a lot of screens based on an sql database using a lot of stored procedures. The app sends '%' as parameter' values where is not a specifically value needed. I have to change this behaviour. The new version of the app will send '' (emty string) insetad of '%'.
I have to found a way to change ALL stored procedures, to dinamically find out char or varchar type parameters and do something like this:
set @param = '%'+@param+'%'
Till now, I have tried to insert thios fragment at the beginning of the procedure
CREATE TABLE #set_exec (strSQL varchar(255) null)
INSERT INTO #set_exec
select strSQL =' set ' + nume_parametru + '= ' + ''''+'%'+'''' +' +'+ nume_parametru+ '+' +''''+'%'+'''' from [dbo].[ListaParametriChar] (OBJECT_NAME(@@PROCID))
DECLARE ParamList CURSOR FOR
SELECT * from #set_exec
OPEN ParamList
FETCH NEXT FROM ParamList INTO @strSQL
WHILE @@FETCH_STATUS = 0
BEGIN
--select @strSQL
EXEC (@strSQL)
FETCH NEXT FROM ParamList into @strSQL
END
CLOSE ParamList
DEALLOCATE ParamList
But it's not working, as the scope of the variable is inside the batch EXEC (@strSQL) and is not passed to outer variable, which is my real parameter.
DO you have any idea how to solve this problem with other approach?
Thanks in advance for any sugestion.
Suggest you rethink this approach, few things striking as odd
😎
1. (OBJECT_NAME(@@PROCID)) will most likely return null, not certain what the purpose is
2. select strSQL =' set ' + nume_parametru + .... is wide open for sql injection, concatenation of parameter+value is a really bad practice.
January 15, 2016 at 2:57 am
No way to avoid explicitly mentioning the parameter name you want to reset in the context of the procedure. So Sp_executesql doesn't help too.
You need explicitly refactor procedures, possibly by some script. Or generate a set of wrapper SPs which will do the job.
January 15, 2016 at 3:05 am
Changing parameter values within a procedure is a really bad idea.
https://www.simple-talk.com/content/article.aspx?article=2280
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply