Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Procedure or function not working correctly Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 11:22 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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

Post #1471946
Posted Wednesday, July 10, 2013 12:37 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 1:04 PM
Points: 2,663, Visits: 5,798
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



Post #1471965
Posted Wednesday, July 10, 2013 1:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 7:15 PM
Points: 1,129, Visits: 1,163
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
Post #1471982
Posted Wednesday, July 10, 2013 4:07 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 789, Visits: 694
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472037
Posted Wednesday, July 10, 2013 3:56 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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
Post #1472385
Posted Wednesday, July 10, 2013 4:19 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 3:23 PM
Points: 789, Visits: 694
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1472392
Posted Wednesday, July 10, 2013 4:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 3, 2014 4:58 PM
Points: 130, Visits: 214
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

Post #1472395
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse