Deferrend Name Resolution for a field in a Stored Procedure

We are doing an upgrade in about a month and changing the account structure
in one of our tables. I am trying to write a routine that will check to see
if we are using the old format or the new format. So I wrote the following
stored procedure:
CREATE PROCEDURE [dbo].[sp_Account_Info] AS
if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
begin
select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
--Do more stuff
end
else
begin
select ACTNUMBR_4 from Account_Table
--Do more stuff
end
In the old format the columns stop at ACTUNUMBR_4, but in the new table
structure(Which has not been implmented yet) we will be adding ACTNUMBR_5
and ACTNUMBR_6. The problem I am having is SQLServer will not let me save
this stored procedure because it says I have an invalid column name. Is
there a way to save the stored procedure even though the new columns do not
yet exist?You could use dynamic SQL (www.sommarskog.se), but perhaps you should consid
er a stable data model
instead, which doesn't require you to add columns over time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Miles C" <mcousens@.clearwater.ca> wrote in message news:eZmKRAHlGHA.408@.TK2MSFTNGP03.phx.g
bl...
> We are doing an upgrade in about a month and changing the account structur
e in one of our tables.
> I am trying to write a routine that will check to see if we are using the
old format or the new
> format. So I wrote the following stored procedure:
> CREATE PROCEDURE [dbo].[sp_Account_Info] AS
> if exists(select COLUMN_NAME = convert(sysname,name) from syscolumns where
name ='ACTNUMBR_6')
> begin
> select ACTNUMBR_5,ACTNUMBR_6 from Account_Table
> --Do more stuff
> end
> else
> begin
> select ACTNUMBR_4 from Account_Table
> --Do more stuff
> end
> In the old format the columns stop at ACTUNUMBR_4, but in the new table st
ructure(Which has not
> been implmented yet) we will be adding ACTNUMBR_5 and ACTNUMBR_6. The pro
blem I am having is
> SQLServer will not let me save this stored procedure because it says I hav
e an invalid column
> name. Is there a way to save the stored procedure even though the new col
umns do not yet exist?
>

Default restrictions and field names...

Hello,
I'm in the task of changing the data type of a column in a SQL Server
2000 database which already has default and check constraints.
Being unable to directly issue an ALTER TABLE command, I guess that the
only way will be to delete the check constraints, the default
constraints, change the data type and then recreate the check and
default constraints updated.
The names of the check constraints and their corresponding columns could
be obtained from the CONSTRAINT_COLUMN_USAGE view. But I still can't get
any system view that related the name of the default constraints and the
name of their corresponding table columns.
Any help will be much appreciated,
KlermanHi
CREATE TABLE Test
(
col DATETIME DEFAULT GETDATE()
)
SELECT scobj.name, cols.name
FROM sysconstraints sc
INNER JOIN sysobjects scobj
ON sc.constid = scobj.id
AND sc.id=OBJECT_ID('Test')
INNER JOIN syscolumns cols
ON sc.id = cols.id
AND sc.colid = cols.colid
GO
ALTER TABLE Test DROP CONSTRAINT DF__Test__col__6C190EBB
"Klerman Gutierrez" <klerman.gutierrez@.gmail.com> wrote in message
news:e2KvNGllFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I'm in the task of changing the data type of a column in a SQL Server 2000
> database which already has default and check constraints.
> Being unable to directly issue an ALTER TABLE command, I guess that the
> only way will be to delete the check constraints, the default constraints,
> change the data type and then recreate the check and default constraints
> updated.
> The names of the check constraints and their corresponding columns could
> be obtained from the CONSTRAINT_COLUMN_USAGE view. But I still can't get
> any system view that related the name of the default constraints and the
> name of their corresponding table columns.
> Any help will be much appreciated,
> Klerman|||Uri,
Based on your suggestion, I wrote this solution. Hope this could help
someone else trying to remove restrictions associated with a field.
Thanks a lot for the help
Klerman
EXEC pa_BorraRestricciones 'TableName', 'ColumnName'
CREATE PROCEDURE pa_BorraRestricciones(@.Tabla SYSNAME, @.Columna SYSNAME)
AS
DECLARE @.Restriccion SYSNAME,
@.strSQL NVARCHAR(2048)
DECLARE Restricciones CURSOR LOCAL FAST_FORWARD FOR SELECT
SYSOBJECTS.NAME FROM (SYSCONSTRAINTS INNER JOIN SYSOBJECTS ON
SYSCONSTRAINTS.constid=SYSOBJECTS.id) INNER JOIN SYSCOLUMNS ON
SYSCONSTRAINTS.colid=SYSCOLUMNS.colid WHERE
SYSCONSTRAINTS.id=OBJECT_ID(@.Tabla) AND SYSCOLUMNS.NAME=@.Columna
OPEN Restricciones
FETCH NEXT FROM Restricciones INTO @.Restriccion
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SET @.strSQL = N'ALTER TABLE ' + @.Tabla + ' DROP CONSTRAINT ' +
@.Restriccion
EXEC (@.strSQL)
FETCH NEXT FROM Restricciones INTO @.Restriccion
END
CLOSE Restricciones
DEALLOCATE Restricciones
RETURN