Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Proc not returning results 1

Status
Not open for further replies.
programmher

programmher

Programmer
May 25, 2000
235
US
Below is my stored procedure:

@Client varchar(75),
@salesman_lastname varchar(50),
@ClientCity varchar(50),
@ClientState varchar(4),
@CSR_LastName varchar(50)

AS
SELECT @Client = @Client + '%',

@salesman_lastname = @salesman_lastname + '%',

@ClientCity = @ClientCity + '%' ,

@CSR_LastName = @CST_LastName + ‘%’



BEGIN

SELECT *

from tbl_ClientOrders WITH (NOLOCK)

where (Client like @Client OR @Client Is Null)

and ( Salesman_lastname like salesman_lastname OR @ salesman_lastname Is Null)

and (ClientCity like @ClientCity OR @ClientCity Is Null)

and (CSR_LastName = @CSR_LastName OR @CSR_LastName Is Null)

RETURN(0)

END


When I execute this with a straight query, I get results. This stored procedure returns no results. What am I missing? I am attempting to allow the user to enter one or more criteria.
 
Sort by date Sort by votes
I think you need to remove the RETURN(0).

Hope this helps.
 
Upvote 0 Downvote
MeanGreen,

I removed the Return(0) but still no results...
 
Upvote 0 Downvote
Is this a typo in your post or in your script:


and ( Salesman_lastname like salesman_lastname OR @ salesman_lastname Is Null)

should be:


and ( Salesman_lastname like @salesman_lastname OR @salesman_lastname Is Null)

note @ added and space between @ and salesman_lastname removed.

-SQLBill
 
Upvote 0 Downvote
create procedure usp_PROCEDURENAME
@Client varchar(75) = NULL,
@salesman_lastname varchar(50) = NULL,
@ClientCity varchar(50) = NULL,
@ClientState varchar(4)= NULL,
@CSR_LastName varchar(50)= NULL

AS
Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK) where '

IF @Client IS NOT NULL
select @sqlstr = @sqlstr + 'Client like ''' + @Client + '%''' + ' and'
IF @salesman_Lastname IS NOT NULL
select @sqlstr = @sqlstr + ' salesman_lastname like ''' + @salesman_lastname + '%''' + ' and'
IF @ClientCity IS NOT NULL
select @sqlstr = @sqlstr + ' ClientCity like ''' + @ClientCity + '%''' + ' and'
IF @ClientState IS NOT NULL
select @sqlstr = @sqlstr + ' ClientState like ''' + @ClientState + '%''' + ' and'
IF @CSR_LastName IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_lastname like ''' + @CSR_lastname + '%'''
IF right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

exec(@sqlstr)
[/B]

This is all assuming you will have at least one good input value. If not, you will need another check for all nulls and return with no values.

I think most of my syntax is correct.

Hope this helps.
 
Upvote 0 Downvote
That worked like a charm!!! Many thanks! I've one last question - I need to add one or two more fields. Is my syntax correct?

create procedure usp_PROCEDURENAME
@Client varchar(75) = NULL,
@salesman_lastname varchar(50) = NULL,
@ClientCity varchar(50) = NULL,
@ClientState varchar(4)= NULL,
@CSR_LastName varchar(50)= NULL
@CSR_EmpNum int= NULL

AS
Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK) where '

IF @Client IS NOT NULL
select @sqlstr = @sqlstr + 'Client like ''' + @Client + '%''' + ' and'
IF @salesman_Lastname IS NOT NULL
select @sqlstr = @sqlstr + ' salesman_lastname like ''' + @salesman_lastname + '%''' + ' and'
IF @ClientCity IS NOT NULL
select @sqlstr = @sqlstr + ' ClientCity like ''' + @ClientCity + '%''' + ' and'
IF @ClientState IS NOT NULL
select @sqlstr = @sqlstr + ' ClientState like ''' + @ClientState + '%''' + ' and'
IF @CSR_LastName IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_lastname like ''' + @CSR_lastname + '%'''+ ' and'
IF @CSR_EmpNum IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_EmpNum = @CSR_EmpNum''
IF right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

 
Upvote 0 Downvote
Almost, you left off the comma at the top and in this case you added @CSR_EmpNum as integer... so you will have to make a minor change:

create procedure usp_PROCEDURENAME
@Client varchar(75) = NULL,
@salesman_lastname varchar(50) = NULL,
@ClientCity varchar(50) = NULL,
@ClientState varchar(4)= NULL,
@CSR_LastName varchar(50)= NULL,
@CSR_EmpNum int= NULL

AS
Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK where '

IF @Client IS NOT NULL
select @sqlstr = @sqlstr + 'Client like ''' + @Client + '%''' + ' and'
IF @salesman_Lastname IS NOT NULL
select @sqlstr = @sqlstr + ' salesman_lastname like ''' + @salesman_lastname + '%''' + ' and'
IF @ClientCity IS NOT NULL
select @sqlstr = @sqlstr + ' ClientCity like ''' + @ClientCity + '%''' + ' and'
IF @ClientState IS NOT NULL
select @sqlstr = @sqlstr + ' ClientState like ''' + @ClientState + '%''' + ' and'
IF @CSR_LastName IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_lastname like ''' + @CSR_lastname + '%'''+ ' and'
IF @CSR_EmpNum IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_EmpNum = ' + convert(varchar(25),@CSR_EmpNum)
IF right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

Hope this helps.

 
Upvote 0 Downvote
MeanGreen -

Helps?!?! You've solved my dilemna!!! Many, many thanks!!!

I've one last question (as my requirements are ever changing).

I now need to add dates to this procedure. Do I convert the datetime data type to a number or directly to a varchar. If so, what is the correct syntax? Something like:

SELECT @EnterDate = convert(varchar(8),@EnterDate,112)
 
Upvote 0 Downvote
MeanGreen,

I need to revise my question.

I now need to include a date range in my declared SELECT statement. Is this syntax correct?

Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK) where enterdate between (convert(varchar(8),@EnterDate,112') AND convert(varchar(8),@EnterDate,112)

This looks like something is amiss - I just can't decipher what...
 
Upvote 0 Downvote
You are close, but remember you are building a string. I think your where clause would look something like this:

where enterdate between (convert(varchar(8),' + @EnterDate + ',112) AND convert(varchar(8),' + @EnterDate+ ',112)'

Hope this helps.
 
Upvote 0 Downvote
MeanGreen,

Here is my finished product:


create procedure usp_PROCEDURENAME
@Client varchar(75) = NULL,
@salesman_lastname varchar(50) = NULL,
@ClientCity varchar(50) = NULL,
@ClientState varchar(4)= NULL,
@CSR_LastName varchar(50)= NULL,
@CSR_EmpNum int= NULL,
@EnterDate datetime,
@ShippedDate datetime

AS
Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK where enterdate between (convert(varchar(8),' + @EnterDate + ',112) AND convert(varchar(8),' + @ShippedDate+ ',112)'

IF @Client IS NOT NULL
select @sqlstr = @sqlstr + 'Client like ''' + @Client + '%''' + ' and'
IF @salesman_Lastname IS NOT NULL
select @sqlstr = @sqlstr + ' salesman_lastname like ''' + @salesman_lastname + '%''' + ' and'
IF @ClientCity IS NOT NULL
select @sqlstr = @sqlstr + ' ClientCity like ''' + @ClientCity + '%''' + ' and'
IF @ClientState IS NOT NULL
select @sqlstr = @sqlstr + ' ClientState like ''' + @ClientState + '%''' + ' and'
IF @CSR_LastName IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_lastname like ''' + @CSR_lastname + '%'''+ ' and'
IF @CSR_EmpNum IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_EmpNum = ' + convert(varchar(25),@CSR_EmpNum)
IF right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

Here is my error:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.


SQL = "{ CALL GetShippedInfo ('','','','', '','','','','','','','nh','10/31/02','12/05/02')}"

I don't see anything amiss. Can you?

Again, THANKS!!!
 
Upvote 0 Downvote
You missed the "and" at the top:

select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK where enterdate between (convert(varchar(8),' + @EnterDate + ',112) AND convert(varchar(8),' + @ShippedDate+ ',112) and '

Hope this helps.
 
Upvote 0 Downvote
MeanGreen,

I pasted the above into my stored proc and still got the below error message:

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'AND'.

Any thoughts or ideas?
 
Upvote 0 Downvote
Try this code:

Create procedure usp_PROCEDURENAME
@Client varchar(75) = NULL,
@salesman_lastname varchar(50) = NULL,
@ClientCity varchar(50) = NULL,
@ClientState varchar(4)= NULL,
@CSR_LastName varchar(50)= NULL,
@CSR_EmpNum int= NULL,
@EnterDate datetime,
@ShippedDate datetime

AS
Declare @SQLSTR varchar(2000)
select @sqlstr = 'SELECT * from tbl_ClientOrders WITH NOLOCK where enterdate between ''' + convert(varchar(12),@EnterDate,101) + ''' AND ''' + convert(varchar(12),@ShippedDate,101) + ''' and'

IF @Client IS NOT NULL
select @sqlstr = @sqlstr + ' Client like ''' + @Client + '%''' + ' and'
IF @salesman_Lastname IS NOT NULL
select @sqlstr = @sqlstr + ' salesman_lastname like ''' + @salesman_lastname + '%''' + ' and'
IF @ClientCity IS NOT NULL
select @sqlstr = @sqlstr + ' ClientCity like ''' + @ClientCity + '%''' + ' and'
IF @ClientState IS NOT NULL
select @sqlstr = @sqlstr + ' ClientState like ''' + @ClientState + '%''' + ' and'
IF @CSR_LastName IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_lastname like ''' + @CSR_lastname + '%'''+ ' and'
IF @CSR_EmpNum IS NOT NULL
select @sqlstr = @sqlstr + ' CSR_EmpNum = ' + convert(varchar(25),@CSR_EmpNum)
IF right(@sqlstr,3) = 'and'
select @sqlstr = left(@sqlstr,datalength(@sqlstr)-3)

Made a few changes for the datetime.

Hope this helps.
 
Upvote 0 Downvote
Dear MeanGreen,

You should change your handle to MeanGreenGuru! That took care of eliminating the error message!!!! Countless thanks!!

By the by, what significance does the -3 have in this command? left(@sqlstr,datalength(@sqlstr)-3)
This is new to me.
 
Upvote 0 Downvote
What that is doing is removing the last "and" if it exist. Basically, the statement says give me the "@sqlstr", but only give me everything to the left excluding the last 3 characters. Datalength tells me how long "@sqlstr" is.

Glad to hear it helps. Vote for me if it helped.
 
Upvote 0 Downvote
MeanGreen,

I marked this post as expert. Is there somewhere else I need to vote???
 
Upvote 0 Downvote
Nope. Thank you very much. Hope you have a nice day. Very glad to hear your procedure is working now.

Good Luck in the future!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Cpreston
  • Locked
  • Question
Group results into one row 3
2
Replies
37
Views
2K
Chris Miller
Chris Miller
briangriffin
  • Locked
  • Question
Function Returning Rowcounts
Replies
1
Views
297
briangriffin
briangriffin
Cpreston
  • Locked
  • Question
Returns wrong results
Replies
1
Views
289
Cpreston
Cpreston
WomanPro
  • Locked
  • Question
How to display the results of my query delimited with pipes?
Replies
9
Views
434
WomanPro
WomanPro
Cpreston
  • Locked
  • Question
Will not show row with Line Type 2 1
Replies
17
Views
769
Cpreston
Cpreston

Part and Inventory Search

Sponsor

Back
Top