Store procedure help ???

RinoDM · May 1st, 2008, 10:13 AM

Hi to all...

I created this store procedure that i pass it a 6 digit number...
but it returnes me nothing...

CREATE PROCEDURE sp_GetAllSKU
    @intSKU int

AS
    select * from PromoDetailID PD
    Join PromoID P On P.PromoID = PD.PromoID
    where PD.PromoSKU like '%@varSKU%'
    and P.PromoActive=1
    and P.PromoLanguage='E'

GO

But if i run it in query analyzer and actually put a number it returnes me info...

select * from PromoDetailID PD
Join PromoID P On P.PromoID = PD.PromoID
where PD.PromoSKU like '%417856%'
and P.PromoActive=1
and P.PromoLanguage='E'

Is there something i'm missing in my SP that i need...

thanking you in advance...

Rino

RinoDM · May 1st, 2008, 10:17 AM

Sorry don't look at '%@varSKU%' i have it the same as declared @intSKU....

joefawcett · May 1st, 2008, 10:20 AM

I would do:

Code:

DECLARE @Search NVARCHAR(20);
SET @Search = '%' + CAST(@intSKU AS NVARCHAR(15)) + '%';
SELECT .... WHERE PD.PromoSKU LIKE @Search....

--

Joe (Microsoft MVP - XML)

RinoDM · May 1st, 2008, 10:27 AM

when i apply the SP, it gives me an error "incorrect syntax near '+'"

rgerald · May 1st, 2008, 10:51 AM

You had:

Code:

    select * from PromoDetailID PD
    Join PromoID P On P.PromoID = PD.PromoID
    where PD.PromoSKU like '%@varSKU%'
    and P.PromoActive=1
    and P.PromoLanguage='E'

Change it as follows:

where PD.PromoSKU like '%' + @varSKU + '%'

Rand

rgerald · May 1st, 2008, 11:13 AM

Oops, one slight difference. Since @varSKU is an integer, you need to do:

WHERE PD.PromoSKU LIKE '%' + CAST(@varSKU AS varchar(10)) + '%'

Rand

RinoDM · May 1st, 2008, 01:54 PM

Perfect it works fine...
One other thing not sure if i should post this question in another section...

i have an <input type="text" ... > textbox that has the 6 digits that i need to bring the variable over to my other page and that's where i connect to SQL and run my SP...
it's working fine put returning all rows...
looks like my variable is not coming over with my page...

how can i bring my variable over to another page???

Thanking you in advance

Rino

rgerald · May 1st, 2008, 02:34 PM

Your other question is more appropriate for a VB, C#, or ASP.NET forum depending upon which you are using.

In other words, I don't know the answer.

:)

Rand

RinoDM · May 1st, 2008, 03:03 PM

thank you for your help...