February 27, 2008 at 10:27 am
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
February 27, 2008 at 10:30 am
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 27, 2008 at 11:52 am
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!;)
February 27, 2008 at 11:52 am
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
February 27, 2008 at 1:09 pm
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[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgFebruary 27, 2008 at 1:22 pm
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!;)
February 27, 2008 at 1:33 pm
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?
February 27, 2008 at 1:59 pm
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!;)
February 27, 2008 at 2:54 pm
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?
February 28, 2008 at 8:27 am
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!;)
February 29, 2008 at 12:05 am
I would try and get rid of the order by, so you don't need to use dynamic sql, and instead let the application perform the sort based on user selection (e.g. clicking on a listbox header)
February 29, 2008 at 9:52 am
I tried everything you guys told me but still did not work so I just send from my c# code the " ' ' " characters.
Thanks a lot.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply