MSSQL 2005 - Add ID value to ID column when INSERTING

dubby (Dave) — Tue, 11 Sep 2007 07:50:29 GMT

// @TableName is obviously the TABLE name u use
// @ColumnName is obviously the COLUMN name u use



---Get next ID number

	DECLARE 

		@ID int

	

	IF (SELECT count(*) FROM @TableName ) > 0

		BEGIN

			SELECT @ID  = max(ColumnName ) from @TableName

			SET @ID = @ID + 1 

		END

	ELSE

	BEGIN

		SET @ID  = 1

	END

MSH host function in SQL server 2005

klumsy (karl prosser) — Sat, 25 Feb 2006 06:31:44 GMT

// part of a sql server CLR function, and example of hosting MSH in sql server 2005



    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString sqlmsh(string input)

    {

        string output;

        Runspace runspace = RunspaceFactory.CreateRunspace();

        runspace.Open();

        Pipeline pipeline = runspace.CreatePipeline(input);

        MshObject myobj =   pipeline.Invoke()[0];

        if (myobj.BaseObject is XmlDocument)

         {

            output = (myobj.BaseObject as XmlDocument).InnerXml;

        } else

            output = myobj.ImmediateBaseObject.ToString();

            

        runspace.Close();

        return new SqlString(output);

    }

DZone Snippets: 2005 code

MSSQL 2005 - Add ID value to ID column when INSERTING

MSH host function in SQL server 2005