Dynamic SQl

Question

Post reply

Dynamic SQl

ouertani neked

SSC-Addicted

Points: 406
More actions
June 17, 2004 at 9:54 am

#108403

Hi everybody,
I'm changing my stored procedure to Dynamic SQL to include the database name as a parameter and I'm facing this problem with the identity columns:
I have SPs that return the identity they had just inserted using the SCOPE_IDENTITY() instruction and i had no problem; however when I changed the SP to Dynamic SQl the SCOPE_IDENTITY() is no longer working and I had to change it with the @@Identity variable
but as you know SCOPE_IDENTITY() is more secure because it is linked to the current scope and I had no risk if any one else (or me my self) will later add any trigger on the trated table .
Is there any solution to keep using SCOPE_IDENTITY() in my SP
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Vijay SJ Valued Member Points: 71 More actions · Answer 1

Hi

Why is that you opted for dynamic sql just for the reason to add a parameter.

Actually dynamic sqls creates the execution plan for every execution.

ouertani neked SSC-Addicted Points: 406 More actions · Answer 2

My SPs will run on many database, and i don't want to keep the name of the database hard coded in any one of my SP so each time i'll have to change the name of the database in all my SPs and functions. I have procedure that call procedure, procedure that call function, procedure that use cursor, DDl instruction, DML instruction...

bobsterboy SSCommitted Points: 1549 More actions · Answer 3

If you need to return a value from dynamic sql, you should use

the sp_executeSQL system stored proc. You can look it up in BOL, but here's a sample script:

DECLARE @nvch500SQLStr as nvarchar(500),

@nvch500ParmStr as nvarchar(500),

@nvch20SegValLocal as nvarchar(20)

--This is the select Statement which allows you to use Dynamic SQL AND also return a value from the statement

SET @nvch500SQLStr = N'SELECT @nvch20SegValOUT = CategoryName FROM Northwind.dbo.categories WHERE CategoryID = 1'

--This is where you declare your variables so you can pass data OUT from the Dynamic SQL Script - Note the OUTPUT clause after declaring the variable

SET @nvch500ParmStr = N'@nvch20SegValOut as nvarchar(20) OUTPUT'

-- To run the SQLStr, you pass the SQLString and then the parameters and then map the SQLString

-- parameters to your local parameters - you will want to tag your local parameters with Local

-- to make it less confusing, but you could use any name for the mapping, just make sure the

-- data types are the same.

EXECUTE sp_executesql @nvch500SQLStr,

@nvch500ParmStr,

@nvch20SegValout = @nvch20SegValLocal OUTPUT

print @nvch20segvalLocal

David Burrows SSC Guru Points: 65142 More actions · Answer 4

as per above, try this

declare @sql nvarchar(100), @idno int

set @sql = 'insert into ' + @databasename + '.dbo.tablea (cola,colb values (valuea,valueb) set @idno = SCOPE_IDENTITY()'

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

select @idno

Far away is close at hand in the images of elsewhere.
Anon.

ouertani neked SSC-Addicted Points: 406 More actions · Answer 5

ouertani neked

SSC-Addicted

Points: 406

June 21, 2004 at 5:23 am

#511190

Thank you VM David, it works!

Mark Barrett Newbie Points: 5 More actions · Answer 6

Hi,

I needed to be able to insert a value into a current year defined field but also needed to return the newly created identity value. From your posts above I managed the following, which works, but my question is it the best way of acheiving the goal?

SP as follows:

ALTER PROCEDURE

DBO.SP_BIDMAN_SYS_ADDNEWRESOURCEITEM

@RESOURCEID

INT = NULL OUTPUT ,

@RESOURCEGROUPID

INT ,

@DESCRIPTION

VARCHAR (255) ,

@UNITTYPE

INT ,

@UNITCOST

MONEY ,

@UNITCHARGERATE

MONEY ,

@userid

INT

AS

DECLARE

@UNITCOST_FIELDNAME VARCHAR (100)

DECLARE

@UNITCHARGERATE_FIELDNAME VARCHAR (100)

DECLARE

@CURRENTDATE DATETIME

DECLARE

@CURRENTYEAR INT

DECLARE

@sql NVARCHAR (1000)

DECLARE

@nvch500SQLStr as nvarchar(500),

@nvch500ParmStr

as nvarchar(500),

@nvch20SegValLocal

as nvarchar(20)

SELECT

@CURRENTDATE = GETDATE()

SELECT

@CURRENTYEAR = YEAR(GETDATE())

SET

@UNITCOST_FIELDNAME = 'UCOST' + CAST(@CURRENTYEAR AS VARCHAR (4))

SET

@UNITCHARGERATE_FIELDNAME = 'UCHARGE' + CAST(@CURRENTYEAR AS VARCHAR (4))

SET

@sql = 'INSERT INTO T_OWNET_BIDRESOURCES_LK (GROUPID, DESCRIPTION, UNITTYPE,' + @UNITCOST_FIELDNAME + ', ' + @UNITCHARGERATE_FIELDNAME + ', ADDEDBY, ADDEDON) '

SET

@sql = @sql + 'SELECT ' + CAST(@RESOURCEGROUPID AS VARCHAR) + ', '

SET

@sql = @sql + '''' + @DESCRIPTION + ''', '

SET

@sql = @sql + CAST(@UNITTYPE AS VARCHAR) + ', '

SET

@sql = @sql + CAST(@UNITCOST AS VARCHAR (50)) + ', '

SET

@sql = @sql + CAST(@UNITCHARGERATE AS VARCHAR (50)) + ', '

SET

@sql = @sql + CAST(@USERID AS VARCHAR(10)) + ', '

SET

@sql = @sql + '''' + CAST(@CURRENTDATE AS VARCHAR) + ''''

SET

@sql = @sql + ';SELECT @nvch20SegValout = scope_identity()'

SET

@nvch500SQLStr = @sql

SET

@nvch500ParmStr = N'@nvch20SegValOut as nvarchar(20) OUTPUT'

EXECUTE

sp_executesql @nvch500SQLStr,

@nvch500ParmStr,

@nvch20SegValout = @nvch20SegValLocal

OUTPUT

SET

@RESOURCEID = @nvch20segvalLocal

RETURN

Thanks in advance.

Mark

bobsterboy SSCommitted Points: 1549 More actions · Answer 7

Three of my main reasons for using dynamic sql is:

1. Need to change the database context.

2. Need to change the tablename ( I think you can do this without dynamic SQL, just don't know how off the top of my head).

3. Need to have dynamic field names.

Most of my other needs are met using UDF's, Stored Procs, and views.

It appears that you are using reason #3. My first suggestion would be to change your database structure to rid yourself of field names that are date centric. You'd be better off with a table that had the year as a field and the data would be in that row for that year. It causes more records, but you aren't having to "lug around" a whole bunch of fields (UCOST1982, UCOST1983, etc).