July 9, 2013 at 11:22 pm
Hi Professionals
I have a procedure which calls a function, the function checks to see if the 3 columns exist, if they do then run a query, if they dont then run a different query.
The problem I am having is no matter what I change the passed in 3 values too it always runs the first part within my procedure which is the = 'Y' part
am i missing something
code below and thanks in advance
Alter procedure [dbo].[checkcolumnsexists]
AS
BEGIN
if dbo.ColumnExists('SOFTWARE_MANUFACTURER','PRODUCT_NAME','PRODUCT_VERSION') = 'Y'
BEGIN
select software_manufacturer,product_name,product_version from dbo.newtable;
END
else
select * from dbo.newtable;
END
ALTER FUNCTION [dbo].[ColumnExists](@SMcol varchar(100),@PNcol varchar(100),@PVcol varchar(100))
RETURNS varchar(1) AS
BEGIN
DECLARE @Result varchar(1);
IF EXISTS
(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE UPPER(TABLE_NAME) = 'NEWTABLE' AND UPPER(COLUMN_NAME) in (@SMcol,@PNcol,@PVcol))
BEGIN
SET @Result = 'Y'
END
ELSE
BEGIN
SET @Result = 'N'
END
RETURN @Result;
END
July 10, 2013 at 12:37 am
Can you post the definition of 'NEWTABLE' as used in your example, and when you say anything is passed in - can you show a call to the function that passes rubbish names to all of the 3 column names and confirm if it returns Y or N.
MikeJohn
July 10, 2013 at 1:23 am
are the columns mutually dependent to occur in the table ?
IF EXISTS
(SELECT * FROM INFORMATION_SCHEMA.Columns WHERE UPPER(TABLE_NAME) = 'NEWTABLE' AND UPPER(COLUMN_NAME) in (@SMcol,@PNcol,@PVcol))
the above code suggests that if any one of these three columns is available return value of the function would be 'Y' - as the "IN" clause usage suggests.. only otherwise, i.e. all three columns are not present, the function would return 'N'.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
July 10, 2013 at 4:07 am
Your function returns 1 if any of the columns exists in the table, not if all columns exists in the table.
But it is not going to work out anyway. If any of the columns are missing, the stored procedeure will not compile, as there is - thankfully! - no deferred name resolution on column names.
Now for the real question: why do you want to do this at all? Maybe you have a good reason - but the solution may be different.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 10, 2013 at 3:56 pm
oh I see
the reason I am wanting to do this is that the user inports spreadsheets into the DB on a regular basis and if these columns exist then I want to run some updates on them
if they dont exist then just ignore running an update
is there any way round this
hope that makes sense
July 10, 2013 at 4:19 pm
I would device the import process so that the columns are always there, but if they are not in the spreadsheet they would be NULL.
A table is supposed to model a distinct entity with a distinct set of attributes, and attempts to go beyond that model works against the idea of a relational database and will put you in trouble.
There are ways to handle the current situation, but they are kludgy. Better to get control over the import process.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 10, 2013 at 4:29 pm
I have managed a workaround, it is a bit longwinded but it seems to work as I have covered every scenario I think.
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[droptable] Script Date: 07/10/2013 14:11:37 ******/
SET ANSI_NULLS ON
GO
Alter procedure [dbo].[checkcolumnsexists]
AS
BEGIN
/* They all exist so exec the full cleanse procedures */
if dbo.ColumnExists('SOFTWARE_MANUFACTURER') = 'Y'
and dbo.ColumnExists('PRODUCT_NAME') = 'Y'
and dbo.ColumnExists('PRODUCT_VERSION') = 'Y'
BEGIN
Exec cleanseDATA;
END
else if
/* If the software manufacturer and product name exists so exec both procedures */
dbo.ColumnExists('SOFTWARE_MANUFACTURER') = 'Y'
and dbo.ColumnExists('PRODUCT_NAME') = 'Y'
BEGIN
-- exec softwaremancleanse;
-- exec productnamecleanse;
END
else if
/* If the software manufacturer and product version exists so exec both procedures */
dbo.ColumnExists('SOFTWARE_MANUFACTURER') = 'Y'
and dbo.ColumnExists('PRODUCT_VERSION') = 'Y'
BEGIN
-- exec softwaremancleanse;
-- exec productversioncleanse;
END
else if
/* If the product name and version exists so exec both procedures */
dbo.ColumnExists('PRODUCT_NAME') = 'Y'
and dbo.ColumnExists('PRODUCT_VERSION') = 'Y'
BEGIN
-- exec productnamecleanse;
-- exec productversioncleanse;
END
else if
/* Only the software manufacturer exists so execute cleanse Software manufacturer procedure */
dbo.ColumnExists('SOFTWARE_MANUFACTURER') = 'Y'
and dbo.ColumnExists('PRODUCT_NAME') = 'N'
and dbo.ColumnExists('PRODUCT_VERSION') = 'N'
BEGIN
-- exec softwaremancleanse;
END
else if
/* Only the product name exists so execute cleanse product name procedure */
dbo.ColumnExists('SOFTWARE_MANUFACTURER') = 'N'
and dbo.ColumnExists('PRODUCT_NAME') = 'Y'
and dbo.ColumnExists('PRODUCT_VERSION') = 'N'
BEGIN
-- exec productnamecleanse;
END
else if
/* Only the product version exists so execute cleanse version procedure */
dbo.ColumnExists('SOFTWARE_MANUFACTURER') = 'N'
and dbo.ColumnExists('PRODUCT_NAME') = 'N'
and dbo.ColumnExists('PRODUCT_VERSION') = 'Y'
BEGIN
-- exec productversioncleanse;
END
END
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply