Databases
StackOverflow2013
Postsdbo
POSQL Server: Alternative for using Exec in Functions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server: Alternative for using Exec in Functions
    primarykey
    data
    text
    <p>I am trying to create a Function that I could call to check which is the next ID in key of each table in my database. I figure out how to do it, but I can not create the function because I get this error: </p> <p>"Invalid use of a side-effecting operator 'INSERT EXEC' within a function."</p> <p>It seems that I can not use Exec in Functions. Which alternatives could I have to get this information?</p> <p>This is my code: </p> <pre><code>CREATE FUNCTION FCN_ProximoID() RETURNS @TablaID Table (Tabla nvarchar(370), ID int) AS BEGIN -- Fill the table variable with the rows for your result set DECLARE @Row INT DECLARE @Filas INT DECLARE @MaxID INT DECLARE @Query As varchar(max) DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128) SET @Filas = (SELECT MAX(Fila) FROM ( SELECT ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS 'Fila', A.TABLE_NAME As Tabla, A.COLUMN_NAME As Columna, A.ORDINAL_POSITION As Indice, B.DATA_TYPE As TipoDato FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A LEFT JOIN INFORMATION_SCHEMA.COLUMNS B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME=B.TABLE_NAME WHERE LEFT(A.TABLE_NAME,3)='EXT' AND OBJECTPROPERTY(OBJECT_ID (constraint_name), 'IsPrimaryKey')=1 AND B.DATA_TYPE='int' ) As Tablas) SET @Row = 1 WHILE (@Row &lt;= @Filas) BEGIN BEGIN SET @TableName = (SELECT Tabla FROM ( SELECT ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS 'Fila', A.TABLE_NAME As Tabla, A.COLUMN_NAME As Columna, A.ORDINAL_POSITION As Indice, B.DATA_TYPE As TipoDato FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A LEFT JOIN INFORMATION_SCHEMA.COLUMNS B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME=B.TABLE_NAME WHERE LEFT(A.TABLE_NAME,3)='EXT' AND OBJECTPROPERTY(OBJECT_ID (constraint_name), 'IsPrimaryKey')=1 AND B.DATA_TYPE='int' ) As Tablas WHERE Fila=@Row) SET @ColumnName = (SELECT Columna FROM ( SELECT ROW_NUMBER() OVER(ORDER BY A.TABLE_NAME) AS 'Fila', A.TABLE_NAME As Tabla, A.COLUMN_NAME As Columna, A.ORDINAL_POSITION As Indice, B.DATA_TYPE As TipoDato FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A LEFT JOIN INFORMATION_SCHEMA.COLUMNS B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME=B.TABLE_NAME WHERE LEFT(A.TABLE_NAME,3)='EXT' AND OBJECTPROPERTY(OBJECT_ID (constraint_name), 'IsPrimaryKey')=1 AND B.DATA_TYPE='int' ) As Tablas WHERE Fila=@Row) INSERT INTO @TablaID EXEC('SELECT ''' + @TableName + ''', ISNULL(MAX(' + @ColumnName + '),0)+1 FROM ' + @TableName ) SET @Row = @Row + 1 END END RETURN END GO </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBWS
    1. USMariPlaza
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server: Alternative for using Exec in Functions
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    2. VO
      singulars
      1. POSQL Server: Alternative for using Exec in Functions
      1. This table or related slice is empty.
      1. VTUpMod
    1. COTry making your ID column a [IDENTITY COLUMN](http://msdn.microsoft.com/en-us/library/ms186775.aspx) and get the last inserted rows' ID using [SCOPE_IDENTITY()](http://technet.microsoft.com/en-us/library/ms190315.aspx).
      singulars
      1. POSQL Server: Alternative for using Exec in Functions
      1. USuser2989408
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload