UDF with Dynamic SQL Query

• fazal.shaikh

  Mr or Mrs. 500

  Points: 522

  More actions

  April 8, 2009 at 5:21 am

  #72005

  Hi all

  I have a UDF in SQL 2000 which is pasted below...

  ALTER function udfGetNextID (@IDField varchar(30), @TableName varchar(50))

  returns numeric

  as

  begin

  declare @Command varchar(1000)

  declare @NextID numeric

  set @Command = 'SELECT @NextID = MAX(' + @IDField + ')+1 FROM dbo.' + @TableName

  execute @Command

  return @NextID-- @Command

  end

  When execute as follows...

  select dbo.udfGetNextID ('myIdField', 'MyTableName')

  it give me the following error...

  Server: Msg 2812, Level 16, State 62, Procedure udfGetNextID, Line 9

  Could not find stored procedure 'SELECT @NextID = MAX(CatID)+1 FROM dbo.MainCategory'.

  Can anyone help me out in this problem....

  Thanks in advance

  Regards

  Fazal.Shaikh

• andrewd.smith

  SSCrazy Eights

  Points: 8444

  More actions

  April 8, 2009 at 6:02 am

  #974186

  1) You cannot use dynamic SQL from user-defined functions written in T-SQL.

  2) You could potentialy avoid the above restriction if this were a stored procedure instead of a UDF, but in addition to several other changes, you would need brackets around the @Command variable: e.g.

  EXECUTE (@Command)

  3) If you are attempting to use this UDF to generate sequential unique identifiers, you are likely to get either duplicate values, or else insert failures if there is a unique constraint on the ID column that you are trying to populate with this function.

  Have you considered using an IDENTITY column instead?

  EDIT: typo corrected

• fazal.shaikh

  Mr or Mrs. 500

  Points: 522

  More actions

  April 8, 2009 at 9:41 pm

  #974817

  Thanks andrewd.smith,

  But I don't want to use Identity Columns.

  To avoid concurrent ID generation this UDF was planned to be called from the insert queries itself

  like...

  INSERT INTO MAINCATEGORY (CATID, CATDESCRIPTION) VALUES (udfGetNextID('CatID', 'MainCategory'))

  newayz

  thanks a lot for reply.

  regards,

  Fazal.Shaikh

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  April 8, 2009 at 10:26 pm

  #974825

  fazal.shaikh (4/8/2009)

  ---

  But I don't want to use Identity Columns.

  Why?

  Are you sure you can control transactions and isolation levels better than SQL Server itself?

  To avoid concurrent ID generation this UDF was planned to be called from the insert queries itself

  like...

  INSERT INTO MAINCATEGORY (CATID, CATDESCRIPTION) VALUES (udfGetNextID('CatID', 'MainCategory'))

  How many deadlocks per hour you're ready to tolerate?

  And what about multi-row inserts, say from file upload?

  _____________
  Code for TallyGenerator