problem with # sign

I have a stored proc that takes a parameter and finds the correct row.
Simple and easy. Unfortunately the data contains various symbols such as #
and -. When I run the proc and try to pass a value that contains a symbol I
get an error. The sp works fine when there are no symbols. Any
suggestions?
Laura KOh and here is the sp.
@.ProductCode nvarchar (5)
AS
SELECT pc.strcolorID, p.intproductID
FROM dbo.tblProducts p join tblJctProductsColors pc
On p.IntProductID=pc.intProductID
WHERE strProductCode = @.ProductCode
Simple sp. but wont work with symbols. I ran the proc with #322 and I get
an error. Sorry I am a newbie. Any help appreciated.
Thanks
Laura K
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>I have a stored proc that takes a parameter and finds the correct row.
>Simple and easy. Unfortunately the data contains various symbols such as #
>and -. When I run the proc and try to pass a value that contains a symbol
>I get an error. The sp works fine when there are no symbols. Any
>suggestions?
> Laura K
>|||What about the table structures? You don't say what the column definitions
are.
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||Hi
You may want to put checks into the client to stop data being passed that
will not be valid. You do not say what the error is, as #322 I would expect
no rows to be returned rather than any specific error.
John
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Oh and here is the sp.
> @.ProductCode nvarchar (5)
> AS
>
> SELECT pc.strcolorID, p.intproductID
> FROM dbo.tblProducts p join tblJctProductsColors pc
> On p.IntProductID=pc.intProductID
> WHERE strProductCode = @.ProductCode
> Simple sp. but wont work with symbols. I ran the proc with #322 and I get
> an error. Sorry I am a newbie. Any help appreciated.
> Thanks
> Laura K
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23du%23BpJoFHA.3316@.TK2MSFTNGP14.phx.gbl...
>|||I get specific errors.
When I use 86-283 there error is
Line 1: Incorrect syntax near '-'
I also have spaces in some of the codes so when I use
spGetProductColor TUK Elvis W
Line 1: Incorrect syntax near 'Elvis'.
Upon closer inspection I have no problems with the # sign thus making the
title of my post obsolete. I do however have problems with the - and
spaces.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:OnpT6tLoFHA.632@.tk2msftngp13.phx.gbl...
> Hi
> You may want to put checks into the client to stop data being passed that
> will not be valid. You do not say what the error is, as #322 I would
> expect no rows to be returned rather than any specific error.
> John
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||The column in question is a brandcode which may include numbers, letters or
what have you depending on the manufactures usage. The datatype is
nvarchar. It is not related to any other table. The table is related by the
productID to many other tables.
Not sure if this is what you are asking.
Laura
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:5rDLe.88549$G8.69248@.text.news.blueyonder.co.uk...
> What about the table structures? You don't say what the column
> definitions are.
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:u1OSNtJoFHA.2472@.TK2MSFTNGP15.phx.gbl...
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.|||Laura,
try
exec spGetProductColor 'TUK Elvis W'
and see if that fixes it.
Stu|||It takes care of the error but now it returns nothing. However when I put
quotes around a value that has no spaces or dashes it still returns a color.
Laura
"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1124043325.349594.239730@.o13g2000cwo.googlegroups.com...
> Laura,
> try
> exec spGetProductColor 'TUK Elvis W'
> and see if that fixes it.
> Stu
>|||Excuse the fact that I am new at this but let me know how to post DDL.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1124041314.536726.261720@.f14g2000cwb.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
>

Problem with "if" statement in Stored Proc

Hi All,

I'm haveing problems with a simple if statement within a stored proc. Here is a snippet of the stored proc

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id
IF @.industry = 31
BEGIN
AND industry = @.industry
END

The error message i get from enterprise manager is:

Error 156: Incorrect syntax near the keyword 'AND'

If i remove the if statement and select and select an 'industry' value other than 31 the it works fine.

Thanks

What exactly are you trying to do?|||

Ok, i have a job search function, that allows the users to filter the jobs by industry. This is done with an industry dropdown list. If the value selected is "All Industries" the value passed is '31', therefore i need to ignore the industry filter and output all the results (job) regardless of what industry they are under.

I originally had the following asp.net (c#) code:

if(Request.QueryString["industry"] != "31"){
SQL += " AND industry = @.industry";
prm = new SqlParameter("@.industry",SqlDbType.VarChar,50);
prm.Direction=ParameterDirection.Input;
prm.Value = Request.QueryString["industry"];
command.Parameters.Add(prm);
}

The above worked fine, but now i'm using a stored proc (becaues i have now implemented a paging function which utilises a stored proc). I could use a "if statement" in asp.net but if i don't pass any value for @.industry, the stored proc will throw an error.

Please let me know if you require further information.

Thanks

|||

Try something like this in your stored procedure:

SELECT *
FROM [tbl_jobs], [tbl_users]
WHERE tbl_jobs.companyid = tbl_users.id AND (industry = @.industry OR @.industry <> 31)

|||

Thanks for your response Terri,

Unfortunately this seems to have the opposite result to what i was trying to achieve. It basically returns all results no matter what industry value is passed (used to filter results), unless the value "31" has been passed. When "31" is passed it outputs nothing (this is because there aren't any records have have an industry equal to "31").

|||

Give this a shot:

SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry = (CASEWHEN @.industry = 31then industryELSE @.industryEND)

|||

This will be more efficient for you (if you only have one or two of these "ALL" type queries):

IF @.industry=31BEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idENDELSEBEGIN SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND industry=@.industryENDAlthough you can also do:SELECT *FROM [tbl_jobs], [tbl_users]WHERE tbl_jobs.companyid = tbl_users.idAND (@.industry=31ORindustry=@.industry)

Which is also more efficient, because the comparison to the field is a fixed number, SQL Server can then use indexes more efficiently -- index range vs complete index scan.

Problem while using stored procedures with temporary tables in dataset

I am trying to generate a report using SQL Server Reporting Service. The dataset is passed the results from a stored procedure. The stored proc contains a temporary table. On exceuting of proc, it fetches the result but when I try to save dataset I get following error message

Invalid object name '#AdditionalParams'. (.Net SqlClient Data Provider)

And no colums are returned in the data set created.

Any help on this would be appreciated.

Thanks in advance

If possible, try using a table variable instead, or create a physical table first.

http://www.odetocode.com/Articles/365.aspx

Here are some workarounds for temp tables.

http://www.sql-server-performance.com/rd_temp_tables.asp

If you have to, try using set fmtonly off in stored procedure.

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

cheers,

Andrew

|||

I exceuted the proc as stored procedure. And latter I added a new field to same dataset, as it didnt had any field because it had thrown error. I refreshed the datset and I got all the dataset fields although initally it showed error and it worked.

But their is essentially problem the way datset are handled in reporting service.

Thanks

Problem while using stored procedures with temporary tables in dataset

I am trying to generate a report using SQL Server Reporting Service. The dataset is passed the results from a stored procedure. The stored proc contains a temporary table. On exceuting of proc, it fetches the result but when I try to save dataset I get following error message

Invalid object name '#AdditionalParams'. (.Net SqlClient Data Provider)

And no colums are returned in the data set created.

Any help on this would be appreciated.

Thanks in advance

If possible, try using a table variable instead, or create a physical table first.

http://www.odetocode.com/Articles/365.aspx

Here are some workarounds for temp tables.

http://www.sql-server-performance.com/rd_temp_tables.asp

If you have to, try using set fmtonly off in stored procedure.

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

cheers,

Andrew

|||

I exceuted the proc as stored procedure. And latter I added a new field to same dataset, as it didnt had any field because it had thrown error. I refreshed the datset and I got all the dataset fields although initally it showed error and it worked.

But their is essentially problem the way datset are handled in reporting service.

Thanks

Problem Using Stored Proc

Hi Folks.. I'm really new to SQL server

I'm writing a stored procedure as:

ALTER PROCEDURE usp_CDE_IssueProcedure1 AS

DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

Now when I run this stored procedure, the table just does not get affected.
But on the other hand when I run the query alone that is:
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

The above query runs absolutely fine.. So can anyone pls tell me whats wrong... I have to get this query in the stored procedure..PLsssssssssssss HELP

Thanks,
Shruti Majithia,
Quinnox Consultancy Services.Hi Shruti,

'ALTER PROC' is used to modify the existing stored procedure without changing the permissions and without affecting the dependent PROCs in the database. So the procedure should be already there in the database. You can use the following check:

if exists (select name from sysobjects where type = 'P' and name = 'usp_CDE_IssueProcedure1')
BEGIN
ALTER PROCEDURE usp_CDE_IssueProcedure1 AS
DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus
END
else
BEGIN
CREATE PROCEDURE usp_CDE_IssueProcedure1 AS
DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus
END
go|||I have the exactly same problem i think..
i have made an sp to update a table.
and everything works with no errors.
exept from that nothing happens.
i the SQL Profiler it says:

exec Content_update @.ContentID = 1, @.Content = N'some text'

but nothing happens

thx in advance

Originally posted by shrutimajithia
Hi Folks.. I'm really new to SQL server

I'm writing a stored procedure as:

ALTER PROCEDURE usp_CDE_IssueProcedure1 AS

DELETE FROM tbl_CDE_IssueTable1
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

Now when I run this stored procedure, the table just does not get affected.
But on the other hand when I run the query alone that is:
INSERT INTO tbl_CDE_IssueTable1 SELECT MONTH(CreatedDate), CorporateStatus, COUNT(IssueID) FROM tbl_IB_Issue WHERE CreatedDate BETWEEN '2004-01-01' AND '2004-12-31' GROUP BY month(CreatedDate), CorporateStatus

The above query runs absolutely fine.. So can anyone pls tell me whats wrong... I have to get this query in the stored procedure..PLsssssssssssss HELP

Thanks,
Shruti Majithia,
Quinnox Consultancy Services.

problem using default value in SP

I created an SP for searching rows in a table.
Create Proc stp_search
@.recID varchar(100) = '%'
As
Select * from tbl1
Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
The SP works if I pass in only 1 recordID, or if I don't pass in anything
(default val of %). But I want to be able to search for multiple recID's, s
o
I created a UDF that returns a table of recID's. If the UDF returns one
recID in the return table, the SP works fine. But if I pass in a string wit
h
2 or more recID's (separated by commas - thus varchar), I get this error
message:
'Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
expression.
The statement has been terminated.'
My goal is to be able to pass in multiple recID's, or just one, or none -
using the default value. My options to achieve this is to have multiple
select statements like this wrapped inside of IF Else:
if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
select * from tbl1 where recID in coalesce(@.recID, recID)
Else
select * from tbl1 where recID in (select * from dbo.udf(@.recID)
The problem is that the actual select statment is quite large and I have 10
parameters. So the IF else thing would be quite verbose (because I am
actually searching on several tables in this SP - one of the params specifie
s
which table).
So my question is if there is a way to implement the functionality of my
UDF which can return more than one value in the subquery (searching on more
than one recID) and still be able to use the default value (with coalesce)
without having to do the If Else thing.
Any suggestions appreciated (I hope I am not stuck with verbose - it would
be a book)
Thanks,
RichSee if this helps.
Arrays and Lists in SQL Server
http://www.sommarskog.se/arrays-in-sql.html
AMB
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>|||I think that my real question should be
Is there a way to implement a form of If Else inside a Where clause?
Declare @.recIDLen int
select @.recIDLen = len(@.recID) - len(replace(recID, ',',''))
select * from tbl1 Where
Case when @.recIDLen = 0 Then recID = coalesce(@.recID, recID)
Case When @.recIDLen > 0 Then recID In (select * from dbo.UDF(@.recID))
Is there a way to do something like this?
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>|||try this
select * from tbl1 Where
(@.recIDLen = 0 and recID = coalesce(@.recID, recID)) or
(recID In (select * from dbo.UDF(@.recID))
"Rich" wrote:
> I think that my real question should be
> Is there a way to implement a form of If Else inside a Where clause?
> Declare @.recIDLen int
> select @.recIDLen = len(@.recID) - len(replace(recID, ',',''))
> select * from tbl1 Where
> Case when @.recIDLen = 0 Then recID = coalesce(@.recID, recID)
> Case When @.recIDLen > 0 Then recID In (select * from dbo.UDF(@.recID))
> Is there a way to do something like this?
>
> "Rich" wrote:
>|||Thanks all for your replies. And Omnibuzz gave me an even better idea. I
will add a varchar parameter which gets the string of recordIDs or default
value of '%'. If this param is '%' then @.recordID int will be used as the
parameter of choice. I was having a problem with the recID column which is
actually an int.
Anyway, this also solved another problem I was having pulling date columns
that contain nulls. I can bypass using a date params with the OR operator.
Thanks again.
"Rich" wrote:

> I created an SP for searching rows in a table.
> Create Proc stp_search
> @.recID varchar(100) = '%'
> As
> Select * from tbl1
> Where recID In (coalesce((select * from dbo.udf(@.recID)), recID))
> The SP works if I pass in only 1 recordID, or if I don't pass in anything
> (default val of %). But I want to be able to search for multiple recID's,
so
> I created a UDF that returns a table of recID's. If the UDF returns one
> recID in the return table, the SP works fine. But if I pass in a string w
ith
> 2 or more recID's (separated by commas - thus varchar), I get this error
> message:
> 'Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.'
> My goal is to be able to pass in multiple recID's, or just one, or none -
> using the default value. My options to achieve this is to have multiple
> select statements like this wrapped inside of IF Else:
> if (select len(@.recID) - len(replace(@.recID, ',', '') = 0
> select * from tbl1 where recID in coalesce(@.recID, recID)
> Else
> select * from tbl1 where recID in (select * from dbo.udf(@.recID)
> The problem is that the actual select statment is quite large and I have 1
0
> parameters. So the IF else thing would be quite verbose (because I am
> actually searching on several tables in this SP - one of the params specif
ies
> which table).
> So my question is if there is a way to implement the functionality of my
> UDF which can return more than one value in the subquery (searching on mor
e
> than one recID) and still be able to use the default value (with coalesce)
> without having to do the If Else thing.
> Any suggestions appreciated (I hope I am not stuck with verbose - it would
> be a book)
> Thanks,
> Rich
>