Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #649

Re: Temp Variables in MS SQL Scripts

Show all headers | View raw

BrianDP (bdp222@gmail.com) writes:
> This is a script that the sql front end client wrote for me,-  it
> inserts a field toward the top of the layout for an autonumber/
> Autoincrement field.  I would like to know if you can replace the name
> Personnl1 toward the top with a variable name and replace the variable
> down into the script, so I wouldnt' have to type that name over and
> over.

Your table design looks funny. Save for the IDENTITY column all columns
are nullable. What would a NULL value in EMPID signify? In LNAME? In
FNAME? Here is a tip: if you don't know what a NULL for a column would
mean, you should probably make that column NOT NULL. Keep in mind that
if you make a column nullable, this also means that you need to add
code to handle the NULL values.

As for your question, you can change:
 
> EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength',
> @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo',
> @level1type=N'TABLE',@level1name=N'PERSONNL1B',
> @level2type=N'COLUMN',@level2name=N'EMPID'
> GO

To 

DECLARE @tablename sysname
SELECT @tablename 'PERSONNL1B'
EXEC sys.sp_addextendedproperty @name=N'AllowZeroLength',
  @value=N'False' , @level0type=N'SCHEMA',@level0name=N'dbo',
  @level1type=N'TABLE',@level1name=@tablename,
  @level2type=N'COLUMN',@level2name=N'EMPID'

Note here that I have deleted the GO. The GO is an instruction to Mgmt 
Studio to send the text to SQL Server as a indepdent batch, and variables
exitsts only for the batch. Thus, you must remove all GO.

This does not cover the CREATE TABLE statement; you cannot have a parameter
for the name there. Nor does it make any sense, since a table is supposed
to describe a unique entity. Thus the likelihood that two tables have
the definition is slim.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Temp Variables in MS SQL Scripts BrianDP <bdp222@gmail.com> - 2011-09-07 10:38 -0700
  Re: Temp Variables in MS SQL Scripts Erland Sommarskog <esquel@sommarskog.se> - 2011-09-07 23:05 +0200
  Re: Temp Variables in MS SQL Scripts "Fred." <ghrno-google@yahoo.com> - 2011-09-13 07:50 -0700

csiph-web