Sending a parameter string type to a sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » SQL Server Newbies » Sending a parameter string type to a sp

12 posts, Page 1 of 2

1 2 »»

Sending a parameter string type to a sp

Rate Topic

Display Mode

Topic Options

Author

Message

yanine

Posted Wednesday, February 27, 2008 10:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 29, 2008 9:41 AM
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

Post #461096

Jason Selburg

Posted Wednesday, February 27, 2008 10:30 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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

Post #461101

TraderSam

Posted Wednesday, February 27, 2008 11:52 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, 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. :)

If it was easy, everybody would be doing it!;)

Post #461166

yanine

Posted Wednesday, February 27, 2008 11:52 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 29, 2008 9:41 AM
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

Post #461167

Jason Selburg

Posted Wednesday, February 27, 2008 1:09 PM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:55 AM
Points: 2,582, Visits: 3,552

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

Post #461231

TraderSam

Posted Wednesday, February 27, 2008 1:22 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, 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!;)

Post #461242

Matt Miller (#4)

Posted Wednesday, February 27, 2008 1:33 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

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?

Post #461252

TraderSam

Posted Wednesday, February 27, 2008 1:59 PM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, 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!;)

Post #461275

Matt Miller (#4)

Posted Wednesday, February 27, 2008 2:54 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:49 PM
Points: 6,998, Visits: 13,946

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?

Post #461304

TraderSam

Posted Thursday, February 28, 2008 8:27 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, 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!;)

Post #461704

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions