SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Procedure or function not working correctly


Procedure or function not working correctly

Author
Message
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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


Mike John
Mike John
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2852 Visits: 5961
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



demonfox
demonfox
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1517 Visits: 1192
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
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
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
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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
Erland Sommarskog
Erland Sommarskog
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
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
Oracle765
Oracle765
SSC-Enthusiastic
SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)SSC-Enthusiastic (186 reputation)

Group: General Forum Members
Points: 186 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search