Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Sending a parameter string type to a sp Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2008 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 27, 2008 10:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Posted Wednesday, February 27, 2008 11:52 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Wednesday, February 27, 2008 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, February 27, 2008 1:09 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:11 PM
Points: 2,717, Visits: 3,857
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
Posted Wednesday, February 27, 2008 1:22 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Wednesday, February 27, 2008 1:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,179, Visits: 15,777
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
Posted Wednesday, February 27, 2008 1:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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
Posted Wednesday, February 27, 2008 2:54 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:25 AM
Points: 7,179, Visits: 15,777
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
Posted Thursday, February 28, 2008 8:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld 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 »

Add to briefcase 12»»

Permissions Expand / Collapse