Site Search:
Sign in | Join | Help

SQL Server (T-SQL)

Comments and notes on SQL Server 2000, 2005, and T-SQL

April 2007 - Posts

  • Creating a Stored Procedure with OUTPUT Parameters

    Creating a stored procedure with an OUTPUT parameter is not a difficult task, but I have to do it quite often and it is convenient for me to blog it here, so that I have an easily accessible example

    Create the stored prodecure like this:

    CREATE PROCEDURE _4P_test
     
    @intInput int,
    @intOutput int OUTPUT

    AS
    set @intOutput = @intInput + 1 

    go

    Call it like this:

    declare @intResult int
    exec _4P_test 3 ,@intResult OUT
    select @intResult

    Posted Apr 20 2007, 08:53 AM by Steve Gray with 16 comment(s)
    [Edit Tags]

  • Renaming a SQL Server

    Following is a T-SQL script you can use to rename your SQL Server after you have changed the computer name. This script will work if you are connected to the default instance or a named instance. After running the script, you must restart SQL Server for the action to complete. 

    DECLARE @machinename sysname, 
 @servername sysname, 
 @instancename sysname 


SELECT 
 @instancename = 
  CASE WHEN charindex('\', @@servername) = 0 THEN '' 
  ELSE SUBSTRING(@@servername, CHARINDEX('\', @@servername), (len(@@servername)+ 1) - CHARINDEX('\', @@servername)) 
  END 
SET @machinename = convert(nvarchar(100), serverproperty('machinename')) + @instancename; 


EXEC sp_dropserver @@servername; 
EXEC sp_addserver @machinename, 'local' 
print 'renamed to ' + @machinename

    IMPORTANT:

    Stop and start the SQL Server to complete the process

    Posted Apr 10 2007, 10:30 AM by Steve Gray with 2 comment(s)
    Filed under: [Edit Tags]