Stored procedure and some odd results

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)

Stored procedure and some odd results

Author

Topic

megamanblue
Starting Member

2 Posts

Posted - 2005-01-27 : 08:56:35

Hi,
This is my first post on the forums here so HELLo everybody! Unfortunately it is not to help someone. I need some help with a problem that has baffled me for two days now and I stuck for ideas. I figured there would be someone here that has had this problem before but couldnt find anything when I searched so I guess I have to start a new topic. Well here it is:

I have written a really simple proc on a SQL 2000 SP3, to use in an Access VBA application I am trying to build. (See below in Bold) When I run the procedure and supply values for the parameters I get 0 rows returned. If I run just the SQL (italics and substitute the parameter placeholders for the same values used when executing the proc I get one row returned. Dont quite understand what is going wrong.

I use the following statement to run the proc:

EXEC sp_CRMODSearchExact emailaddress , 'you@you.com'

Thanks for any help you can offer.

ALTER PROCEDURE

sp_CRMODSearchExact @column nvarchar (50) , @criteria nvarchar (255)

AS
INSERT INTO EXTRACTION

SELECT

SUPPORTER.suppID ,
commonurn ,
isnull(firstname , 'None Available') as firstname ,
isnull(lastname , 'None Available') as lastname ,
isnull(HouseNo , 'None Available') as HouseNo ,
isnull(AddressLine1 , 'None Available') as AddressLine1 ,
isnull(AddressLine2 , 'None Available') as AddressLine2,
isnull(AddressLine3 , 'None Available') as AddressLine3 ,
isnull(AddressLine4 , 'None Available') as AddressLine4 ,
isnull(Town , 'None Available') as Town ,
isnull(County , 'None Available') as County ,
isnull(Postcode , 'None Available') as Postcode ,
isnull(emailAddress , 'None Available') as emailAddress ,
isnull(TelephoneNumber , 'None Available') as Telephone ,
isnull(mobileNumber , 'None Available') as mobileNumber ,
isnull(faxNumber , 'None Available') as faxnumber ,
deceased ,
Goneaway ,
dataprotection

FROM

SUPPORTER LEFT JOIN COMMUNICATIONS

ON

SUPPORTER.suppid = COMMUNICATIONS.suppid

WHERE

@column = @criteria

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-27 : 09:05:31

WHERE
@column = @criteria
doesnt work like that

you can't do it like this.
you where evaluates if 'emailaddress' (the string not the column) equals 'you@you.com'
so it returns false for each row and that's why you don0t get anything back.

for this to work you'll need to use dynamic sql:
exec('select ... where ' + @column + '=' + @criteria) -- or use sp_executeSql to parametrize the query. look it up in BOL

Go with the flow & have fun! Else fight the flow

megamanblue
Starting Member

2 Posts

Posted - 2005-01-27 : 09:08:11

Thanks for that dude... You just saved me a week of trying to get a stored procedure parameter to accept a column. Genius.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources