error in dynamic sql SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

error in dynamic sql SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-08 : 21:07:22

This is my first dynamic SP, I am going bald trying to get this right... I am getting a "INVALID COLUMN NAME" error when I pass info to @nameOnline. Can anybody give me direction here? Thanks alot

MIke

create PROCEDURE select_search
(
@Page int,
@RecsPerPage int,
@genderID int = NULL,
@nameOnline varchar(15) = NULL,
@specifyName int = NULL,
@sexualityID int = NULL,
@minAge int = NULL,
@maxAge int = NULL,
@postalCode varchar(7) = NULL,
@stateProvID int = NULL,
@countryID int = NULL,
@minRating int = NULL,
@maxRating int = NULL,
@statusID int = NULL,
@bodyTypeID int = NULL,
@eyeColorID int = NULL,
@hairColorID int = NULL,
@heightFeet int = NULL,
@heightInches int = NULL,
@educationID int = NULL,
@employmentID int = NULL,
@drinkID int = NULL,
@smokeID int = NULL,
@hobbies varchar(50) = NULL,
@memberSince datetime = NULL,
@sort_order int = NULL
)

AS

DECLARE @clause varchar(1000)

IF (@genderID = '' AND @nameOnline = '' AND @sexualityID = '' AND @minAge = '' AND @maxAge = '' AND @postalCode = '' AND @stateProvID = '' AND @countryID = '' AND @minRating = '' AND @maxRating ='' AND @statusID = '' AND @bodyTypeID = '' AND @eyeColorID = '' AND @hairColorID = '' AND @heightFeet = '' AND @heightInches = '' AND @educationID = '' AND @employmentID = '' AND @drinkID = '' AND @smokeID = '' AND @hobbies = '' AND @memberSince = '')

BEGIN
SET @clause = 'SELECT TOP 200 userID, ,age, nameOnline, points, votes, date FROM tblUserDetails WHERE active IS NOT NULL '
END

ELSE

BEGIN
SET @clause = 'SELECT TOP 200 userID,age,nameOnline, points, votes, date FROM tblUserDetails WHERE active IS NOT NULL '

IF @genderID <> ''
BEGIN
SET @clause = @clause + ' AND genderID = ' + cast(@genderID AS VARCHAR(1))
END

IF @nameOnline <> ''
BEGIN
SET @clause = @clause + ' AND nameOnline = ' + cast(@nameOnline as varchar(15))
END
IF @sexualityID <> ''
BEGIN
SET @clause = @clause + ' AND sexualityID = ' + cast(@sexualityID AS VARCHAR(1))
END

IF @minAge <> -1
BEGIN
SET @clause = @clause + ' AND age > ' + cast(@minAge AS VARCHAR(2))
END

IF @maxAge <> -1
BEGIN
SET @clause = @clause + ' AND age < ' + cast(@maxAge AS VARCHAR(2))
END

IF @postalCode <> ''
BEGIN
SET @clause = @clause + ' AND postalCode = ' + cast(@postalCode AS VARCHAR(7))
END

IF @stateProvID <> ''
BEGIN
SET @clause = @clause + ' AND stateProvID = ' + cast(@stateProvID AS VARCHAR(2))
END

IF @countryID <> ''
BEGIN
SET @clause = @clause + ' AND countryID = ' + cast(@countryID AS VARCHAR(3))
END

IF @minRating <> ''
BEGIN
SET @clause = @clause + ' AND (points/votes) > ' + cast(@minRating AS VARCHAR(3))
END

IF @maxRating <> ''
BEGIN
SET @clause = @clause + ' AND (points/votes) < ' + cast(@maxRating AS VARCHAR(3))
END

IF @statusID <> ''
BEGIN
SET @clause = @clause + ' AND statusID = ' + cast(@statusID AS VARCHAR(1))
END

IF @bodyTypeID <> ''
BEGIN
SET @clause = @clause + ' AND bodyTypeID = ' + cast(@bodyTypeID AS VARCHAR(1))
END

IF @eyeColorID <> ''
BEGIN
SET @clause = @clause + ' AND eyeColorID = ' + cast(@eyeColorID AS VARCHAR(1))
END

IF @hairColorID <> ''
BEGIN
SET @clause = @clause + ' AND hairColorID = ' + cast(@hairColorID AS VARCHAR(1))
END

IF @heightFeet <> ''
BEGIN
SET @clause = @clause + ' AND heightFeet = ' + cast(@heightFeet AS VARCHAR(1))
END

IF @heightInches <> ''
BEGIN
SET @clause = @clause + ' AND heightInches = ' + cast(@heightInches AS VARCHAR(2))
END

IF @educationID <> ''
BEGIN
SET @clause = @clause + ' AND educationID = ' + cast(@educationID AS VARCHAR(1))
END

IF @employmentID <> ''
BEGIN
SET @clause = @clause + ' AND employmentID = ' + cast(@employmentID AS VARCHAR(1))
END

IF @drinkID <> ''
BEGIN
SET @clause = @clause + ' AND drinkID = ' + cast(@drinkID AS VARCHAR(1))
END

IF @smokeID <> ''
BEGIN
SET @clause = @clause + ' AND smokeID = ' + cast(@smokeID AS VARCHAR(1))
END

IF @memberSince <> ''
BEGIN
SET @clause = @clause + ' AND date > @memberSince '
END

IF @sort_order = '1'
BEGIN
SET @clause = @clause + 'ORDER BY date DESC '
END

IF @sort_order = '2'
BEGIN
SET @clause = @clause + ' ORDER BY (points/votes) DESC'
END

END

exec(@clause)

GO

Nazim
A custom title

1408 Posts

Posted - 2002-03-09 : 01:42:34

Hi Mike,

Sorry to say , your code doesnt good enough.

instead of using multiple if's trying to check for a value. you could have been better off using Case statement
Eg:

declare @muser varchar(44)
select * from users
where name like case isnull(@muser,0) when 0 then name else @muser end

i would suggest you to print your sql statement after you set @nameonline ,copy and run the printed statement in QueryAnalyzer. you can have a better idea of what exactly is going wrong .
HTH

--------------------------------------------------------------

Edited by - Nazim on 03/09/2002 01:56:41

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-03-11 : 17:30:22

so continue passing the variables the way I am?

Thanks
(confused)mike

:D

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 17:54:44

You could do this instead:

CREATE PROCEDURE select_search
(
@Page int,
@RecsPerPage int,
@genderID int = NULL,
@nameOnline varchar(15) = NULL,
@specifyName int = NULL,
@sexualityID int = NULL,
@minAge int = NULL,
@maxAge int = NULL,
@postalCode varchar(7) = NULL,
@stateProvID int = NULL,
@countryID int = NULL,
@minRating int = NULL,
@maxRating int = NULL,
@statusID int = NULL,
@bodyTypeID int = NULL,
@eyeColorID int = NULL,
@hairColorID int = NULL,
@heightFeet int = NULL,
@heightInches int = NULL,
@educationID int = NULL,
@employmentID int = NULL,
@drinkID int = NULL,
@smokeID int = NULL,
@hobbies varchar(50) = NULL,
@memberSince datetime = NULL,
@sort_order int = NULL
)

AS

SELECT TOP 200 userID,age,nameOnline, points, votes, date
FROM tblUserDetails
WHERE active IS NOT NULL
AND genderID = IsNull(@genderID,genderID)
AND nameOnline = IsNull(@nameOnline, nameonline)
AND sexualityID = IsNull(@sexualityID, sexualityID)
AND age Between IsNull(@minAge, age) AND IsNull(@maxAge, age)
AND postalCode = IsNull(@postalCode, postalCode)
AND stateProvID = IsNull(@stateProvID, stateProvID)
AND countryID = IsNull(@countryID, countryID)
AND (points/votes) Between IsNull(@minRating, (points/votes)) AND IsNull(@maxRating,(points/votes))
AND statusID = IsNull(@statusID, statusID)
AND bodyTypeID = IsNull(@bodyTypeID,bodyTypeID)
AND eyeColorID = IsNull(@eyeColorID,eyeColorID))
AND hairColorID = IsNull(@hairColorID,hairColorID)
AND heightFeet = IsNull(@heightFeet,heightFeet)
AND heightInches = IsNull(@heightInches,heightInches)
AND educationID = IsNull(@educationID,educationID)
AND employmentID = IsNull(@employmentID,employmentID)
AND drinkID = IsNull(@drinkID,drinkID)
AND smokeID = IsNull(@smokeID,smokeID)
AND date >= IsNull(@memberSince, date)
ORDER BY CASE @sort_order
WHEN '1' THEN DateDiff(dd, date, getdate())
WHEN '2' THEN (points/votes)
ELSE 0 END DESC

The only difference is that this won't work correctly if you pass the parameters as empty strings (''); you'd have to pass them as Null or not at all (since they default to Null anyway).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources