Working with strings wihtin Stored Procedures

[bgreenhouse]

Hi there

I'm developing a page that updates a table through a stored procedure. Rather than just send the variables and plug them into the statement, I wanted to create the statement the web server and then send that string to the stored procedure. To avoid having a stored procedure that will simply execute any TSQL string that is sent it's way (probably a bad idea), I wanted to have UPDATE TABLENAME hardcoded into the stored procedure, along with the where part of the update clause. I figure that this should limit the amount of tampering that can be done through this sp. My problem is, I'm not quite sure how to concatenate and execute the TSQL string in the stored procedure. If I was in an ASP, I would simply concatenate the string and then execute it. How would I go about this in a stored procedure?

I have tried:

"UPDATE TABLENAME & @vTSQL1" (vTSQL1 is the SET part of the update command), planning to follow it with "& WHERE CARD_NUM = & @cCard_Num", but it doesn't like it (I know it's not quite right too, but can't figure out what to do). Is this sort of thing even possible?

Thanks

Ben

 

[chiph]

You know that by doing this you lose the benefit of having pre-compiled SQL, which results in slow performance?

Chip H.

 

[bgreenhouse]

Thanks Chip, that's a good point. My problem is that rather than update every field, I was just going to update the fileds that were changed (sensed using hidden fields whose values changed from "Y" to "N" when a mainfield was changed). This was in an effort to increase performance. If I do this, I can't use precompiled SQL, as the SQL statement is going to be different depending on what fields were changed.

Which do you think is a bigger slow down - Unecessarily updating fields (we're talking maximum of 23 fields here), or non pre-compiled SQL?

Thanks

Ben

 

[tlbroadbent]

Ben,

In SQL you simply concatenate the strings and execute them.

Declare @sql nvarchar(2000)

Set @sql = 'UPDATE TABLENAME ' + @vTSQL1 +
' WHERE CARD_NUM = ' + @cCard_Num

Exec(@sql) OR
Exec sp_executesql @sql Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Selva123]

Hi

If I use above method, the following sql is not working
"Select @tempvar = empcode from employee".

What could be the problem?

 

[tlbroadbent]

Which method are you using? Can you post more of your code? I'm not clear from the snippet you posted what you want to accomplish. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bgreenhouse]

Terry:

Any idea as to the performance aspect?

Thanks

Ben

 

[chiph]

> Which do you think is a bigger slow down - Unecessarily updating fields (we're talking maximum of 23 fields here), or non pre-compiled SQL? <

I'd say updating all fields is faster, assuming that none of the 23 fields are especially large.

Terry - what do you think?

Chip H.

 

[tlbroadbent]

Quite frankly, I feel the whole idea of pre-compiled procedures or queries is overblown. Reusing compiled code is probably helpful when you have large, complex stored procedures or in a high transaction environment. In our environment, I don't think anyone notices the few milliseconds required to compile most queries, scripts and procedures.

Nor do I think they will notice the difference between updating 3 or 23 columns especially if doing one record at a time. You'll hardly be able to measure the difference. You can test various scenarios in Query Analyzer with Set Statistics Time checked on.

I don't use stored procs becasue they are pre-compiled. I use them for ease of maintenance, the power they provide, the security, etc. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Selva123]

Hi Terry,

I am using the set of codes for getting the empcode into
one variable.I am getting tablename, where condition & orderby as an input parameters.

set @sql = 'declare tempcursor cursor for Select empcode from ' + @tablename + ' where '+ @wherecondition + @Orderby

Exec (@sql)

OPEN tempcursor
FETCH NEXT FROM tempcursor INTO @tempvar
CLOSE tempcursor
DEALLOCATE tempcursor

Instead If I build a string & execute,
it is sufficient.

set @sql = 'Select @tempvar = empcode from ' + @tablename + ' where '+ @wherecondition + @Orderby

I tried using &quot;Exec(@sql)&quot; and &quot;Exec sp_executesql @sql&quot;

But I am not getting the result.

But how to do it?

Thanks,
Selva Balaji.

 

[tlbroadbent]

Selva,

Here is code that does what you want using sp_executesql.

declare @tempvar int, @sql nvarchar(4000)
set @tempvar=0

set @sql='Select @tempvar=empcode from ' + @tablename +
' where '+ @wherecondition + ' ' + @Orderby

exec sp_executesql @sql,
N'@tempvar int output',
@tempvar output

Print @tempvar Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.