Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sending a parameter string type to a sp


Sending a parameter string type to a sp

Author
Message
yanine
yanine
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
Hi everybody, I have a problem, I´m sending a string parameter from my c# code to a sp for wich I´m using char(39) to replace the " ' " character but I keep getting an error.

declare @representative varchar(255)
set @representative= 'Victaulic'
exec ('
select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax
from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r
where me.Member_Id = r.Member_Id
and m.Manufacturer_Id = r.Manufacturer_Id
and m.Manufacturer_Id = mc.Manufacturer_Id
AND c.Category_Id = mc.Category_Id
and me.Member_Name = char(39)+'+ @representative + '+char(39)
group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax')

Message Error:
Server: Msg 207, Level 16, State 3, Line 2
The column name 'Victaulic' is not valid.

I tryed moving the + signs but nothing seems to work, please help me out.
Greetings, Yanine
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2989 Visits: 4106
You don't need to replace them.


declare @representative varchar(255)
set @representative= 'Victaulic'
exec ('
select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax
from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r
where me.Member_Id = r.Member_Id
and m.Manufacturer_Id = r.Manufacturer_Id
and m.Manufacturer_Id = mc.Manufacturer_Id
AND c.Category_Id = mc.Category_Id
and me.Member_Name = '+ @representative + '
group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax')



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
TraderSam
TraderSam
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 565
Yep, your variable is already set as a varchar (string). You simply concatenate it to the other string in the dynamic sql as the previous post shows. Smile

If it was easy, everybody would be doing it!Wink
yanine
yanine
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 7
Thanks a lot, but actually I send the parameter from the c# code without the ' ' characters, I put the
set @representative= 'Victaulic' as an example the actual sp is:

exec SP_SearchByCriteria2 null,null,'BAY ASSOCIATES, INC.',''

alter proc SP_SearchByCriteria
@catid int,
@manuid int,
@representative varchar(255),
@SORT VARCHAR(255)
as


if(@catid is not null and @manuid is not null and @representative !='')
begin
exec ('
select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax
from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r
where me.Member_Id = r.Member_Id
and m.Manufacturer_Id = r.Manufacturer_Id
and m.Manufacturer_Id = mc.Manufacturer_Id
AND c.Category_Id = mc.Category_Id
and c.Category_Id =' + @catid+
' and m.Manufacturer_Id=' + @manuid +
' and me.Member_Name = '+@representative +
' group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax
order by me.'+@sort )
end

if(@catid is null and @manuid is null and @representative !='')
begin
exec ('
select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax
from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r
where me.Member_Id = r.Member_Id
and m.Manufacturer_Id = r.Manufacturer_Id
and m.Manufacturer_Id = mc.Manufacturer_Id
AND c.Category_Id = mc.Category_Id
and me.Member_Name = char(39)'+ @representative + '+char(39)
group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax
order by me.'+@sort )
end
Jason Selburg
Jason Selburg
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2989 Visits: 4106
My previous post should still work.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
TraderSam
TraderSam
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 565
If the code you posted is your real stored proc, the first 'if' statement does not have the 'char(39)', but the 'char(39)' is still in the query under the 2nd if statement. It needs to be changed in both queries.

If it was easy, everybody would be doing it!Wink
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8523 Visits: 18280
Your issue is in bold
yanine (2/27/2008)

declare @representative varchar(255)
set @representative= 'Victaulic'
exec ('
select me.Member_Id, UPPER(me.Member_Name)as Member_Name, me.Member_City, me.Member_State, me.Member_Fax
from members me, Manufacturers m, Manufacturers_Categories mc, Categories c, Representations r
where me.Member_Id = r.Member_Id
and m.Manufacturer_Id = r.Manufacturer_Id
and m.Manufacturer_Id = mc.Manufacturer_Id
AND c.Category_Id = mc.Category_Id
and me.Member_Name = '+char(39)+ @representative +char(39)+ '
group by me.Member_Id, me.Member_Name, me.Member_City, me.Member_State, me.Member_Fax')

Message Error:
Server: Msg 207, Level 16, State 3, Line 2
The column name 'Victaulic' is not valid.

I tryed moving the + signs but nothing seems to work, please help me out.
Greetings, Yanine

Render the Char(39) into the SQL string, not as part of the T-SQL itself.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
TraderSam
TraderSam
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 565
Sorry about that, my bad...I jumped too quick.

Matt is correct with the 'char(39)'. I still could not get a similar construct to run in the 'exec' statement.

I had to pull the sql out in to a separate variable, build the sql in it, then exec(@sql).
declare @username varchar(30)
select @username = 'AAATestUser'
declare @sql varchar(1000)
select @sql = 'select * from userstable where username = ' + char(39) + @username + char(39) -- (all of that on one row)

exec(@sql)

If I tried to do the exec and build the string at the same time I kept getting errors, running in Query Analyzer. I also checked an SP I wrote a some time ago that is very similar (dynamic sql) and I had constructed it in a variable and then exec(variable), so I probably ran into this before.

One of those days.

If it was easy, everybody would be doing it!Wink
Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8523 Visits: 18280
Trader - just print the @SQL first - your error will likely pop right up at ya... If you don't see it - paste it into a new query window and see what the parser has to say about it.

Meaning

...

Print (@SQL)

Exec (@SQL)


Also - You can't "build and execute" at the same time. EXEC wants a static string (meaning - something fully built before you pass it to execute), not some concatenation operation happening inside the EXEC statement.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
TraderSam
TraderSam
Old Hand
Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)Old Hand (317 reputation)

Group: General Forum Members
Points: 317 Visits: 565

Also - You can't "build and execute" at the same time. EXEC wants a static string (meaning - something fully built before you pass it to execute), not some concatenation operation happening inside the EXEC statement.


AHA! This is the part I did not know, but figured out thru trial and error. My SQL was good, it just wasn't going to work by constructing it within the param list for the 'exec'.

The funny thing is that BOL (SQL 2000) contradicts this (nothing new there)...it says you can concatenate stuff together inside the EXECUTE string...

From BOL...
EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

EXEC ('DROP TABLE ' + @tablename)

Perhaps there was something in my test code that it specifically did not like, such as the 'char(39)' because it absolutely would not run until I put it in a variable first, like you said Matt. I will test further and see...it isn't a big deal, but you like to know these things for future reference.

If it was easy, everybody would be doing it!Wink
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