Please help, is there anything wrong with my SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Please help, is there anything wrong with my SP

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 15:05:50

Please i am trying to use the following stored procedure for doing the search.(want to make it dynamic where condition.) i am want to call this Sp from asp.net form.
Thank you very much.

CREATE PROCEDURE dbo.USP_Searchrecords

@ProgNO nvarchar(50) = null,
@ProjNO nvarchar(50) = null ,
@ContractNO nvarchar(50) = null

AS

DECLARE @sqlselect nvarchar(4000),
@sqlwhere nvarchar(4000),
@strsql nvarchar(2000)

SELECT @sqlselect = 'SELECT cnid,progno,projno,contractno,cntitle FROM TAB_ccsNetCN'

If @ProgNO IS NOT NULL
BEGIN
select strsql = 'WHERE ProgNO = ' + '@ProgNO'
END

If @ProjNO IS NOT NULL
BEGIN
If @strsql IS NOT NULL
BEGIN
select strsql = @strsql + ' and ProjNO =' + '@ProjNO'
END
ELSE
BEGIN
select strsql = ' WHERE ProjNO =' + '@ProjNO'
END
END

If @ContractNO IS NOT NULL
BEGIN
If @strsql IS NOT NULL
BEGIN
select strsql = @strsql + ' and ContractNO =' + '@ContractNO'
END
ELSE
BEGIN
select strsql = ' WHERE ContractNO =' + '@ContractNO'
END
END

-- Execute the SQL statement
EXEC(@sqlselect + @strsql)
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 15:09:39

What isn't working with it?

And you don't need dynamic SQL for this. You can do a dynamic WHERE clause using COALESCE. Check out the articles here for more information.

Tara

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 15:25:02

Hello Tara,
You mentioned this:

WHERE clause using COALESCE. Check out the articles here for more information.

I made a search COALESCE, but i don't find anything.

And when i execute that stored procedure i am getting just this:
strsql
WHERE ProgNO = @ProgNO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 15:28:11

Here is the article:

http://www.sqlteam.com/item.asp?ItemID=2077

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 17:23:51

Instead of your EXEC statement, print out the variables so that you can analyze what is going on.

Instead of:

EXEC(@sqlselect + @strsql)

Do this:

PRINT @sqlselect
PRINT @strsql
PRINT @sqlselect + @strsql

Do those look badly formed?

Tara

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 17:52:33

Hello Tara,
Sorry, moderator has locked the thread, so i am continuing on this thread.
I am getting the following: when i do the print:
SELECT * FROM Tab_ccsnetcn WHERE ProgNO = @ProgNO and ProjNO = @ProjNO and ContractNO = @ContractNO
************
Now you have asked me why i am checking the null two times at projno.
First i check projno is it null or not, if it is not null then get into the if stetment and then check the sql1 string is that null or not if sql1 strin is not null that means i have to include this field with starting and, if the sql1 is null that means this is going to be the first parameter starting with where.
**********
If @ProjNO IS NOT NULL
BEGIN
If @sql1 IS NOT NULL
BEGIN
select @sql1 = @sql1 + ' and ProjNO = @ProjNO'
END
ELSE
BEGIN
select @sql1 = ' WHERE ProjNO = @ProjNO'
END
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 17:55:08

That doesn't make sense to me.

Just rip this part out:

If @ProjNO IS NOT NULL
BEGIN
If @strsql IS NOT NULL
BEGIN
select strsql = @strsql + ' and ProjNO =' + '@ProjNO'
END
ELSE
BEGIN
select strsql = ' WHERE ProjNO =' + '@ProjNO'
END
END

Remove that from your code.

Also, you should really use COALESCE instead of dynamic SQL here. Dynamic SQL suffers from bad security and performance problems. With COALESCE, you won't have either of these problems and your code will be more manageable and readable.

Tara

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 18:00:39

Hello Tara,

I am actually trying to do this:

if the user fills all the three criteria (progno, projno, contractno)
then the query should be like this:

SELECT * FROM Tab_ccsnetcn WHERE ProgNO = 'prog1' and ProjNO = 'proj1' and ContractNO = 'ctr3'
*****************
if user only fills two criteria prog and contract then: query has to be like this:

SELECT * FROM Tab_ccsnetcn WHERE ProgNO = 'prg1' and ContractNO = 'ctr6'

**********
if the user only fills the proj then

SELECT * FROM Tab_ccsnetcn WHERE ProjNO = 'projectNY'

***********
So like that it has to be totally dynamic.
by checking the users filled information my sp should prepare the where condition totally.
Thank you.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 18:04:57

So what isn't working properly? Are you getting an error? Is the SELECT statement not being formed correctly?

Tara

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-23 : 18:11:07

Tara,

I am totally new to Stored procedures.

I really appreciate if you can help me out on this.

I can see you are the senior most person on this forum.

Thank you very much.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-23 : 18:12:29

You haven't answered my questions.

Tara

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-23 : 22:52:53

you could use a combination of case and coalesce.

quote:

COALESCE
Returns the first nonnull expression among its arguments.

Syntax
COALESCE ( expression [ ,...n ] )

Arguments
expression

Is an expression of any type.

n

Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types
Returns the same value as expression.

Remarks
If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
WHEN (expression1 IS NOT NULL) THEN expression1
...
WHEN (expressionN IS NOT NULL) THEN expressionN
ELSE NULL

CASE
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.

The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

Searched CASE function:

CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

quote:

--------------------
keeping it simple...

anuradhay
Starting Member

41 Posts

Posted - 2004-11-23 : 23:31:59

Try this . this is working.

EXEC dbo.USP_Searchrecords null,'456',null
CREATE PROCEDURE dbo.USP_Searchrecords1

@ProgNO nvarchar(50) = null,
@ProjNO nvarchar(50) = null ,
@ContractNO nvarchar(50) = null

AS

DECLARE @sqlselect nvarchar(4000),
@sqlwhere nvarchar(4000),
@strsql nvarchar(2000)

--SELECT @sqlselect = 'SELECT cnid,progno,projno,contractno,cntitle FROM TAB_ccsNetCN'
SELECT progno, projno, contractno
FROM TAB_ccsNetCN ( NOLOCK )
WHERE ProgNO = COALESCE(@ProgNO, ProgNo)
AND ProjNO = COALESCE(@ProjNO, ProjNO)
AND ContractNO = COALESCE(@ContractNO, ContractNO)

GO

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-24 : 10:38:57

Hello Anuradha,
When i am only filling the contractno = 'cnt3' and executed it is not getting the records, but there are records with cnt3 for contractno field.
and when i fill all the parameters it is working fine.
But when i fill only the projno and contractno field it is not working.

Thank you very much.

quote:
Originally posted by anuradhay

Try this . this is working.

EXEC dbo.USP_Searchrecords null,'456',null
CREATE PROCEDURE dbo.USP_Searchrecords1

@ProgNO nvarchar(50) = null,
@ProjNO nvarchar(50) = null ,
@ContractNO nvarchar(50) = null

AS

DECLARE @sqlselect nvarchar(4000),
@sqlwhere nvarchar(4000),
@strsql nvarchar(2000)

--SELECT @sqlselect = 'SELECT cnid,progno,projno,contractno,cntitle FROM TAB_ccsNetCN'
SELECT progno, projno, contractno
FROM TAB_ccsNetCN ( NOLOCK )
WHERE ProgNO = COALESCE(@ProgNO, ProgNo)
AND ProjNO = COALESCE(@ProjNO, ProjNO)
AND ContractNO = COALESCE(@ContractNO, ContractNO)

GO

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-24 : 10:43:01

Sorry, i forgot to include this:
when i fill all the three parameters then it is working fine.

If i miss a single parameter it is failing.
****For example:
if i only fill contractno and leave the rest of the two fields,
then the query has to be
select * from table where contractno = @contractno

if i select only two field projno and contract no, then:
select * from table where projno = @projno and contractno=@contractno

Thank you very much.

quote:
Originally posted by anuradhay

Try this . this is working.

EXEC dbo.USP_Searchrecords null,'456',null
CREATE PROCEDURE dbo.USP_Searchrecords1

@ProgNO nvarchar(50) = null,
@ProjNO nvarchar(50) = null ,
@ContractNO nvarchar(50) = null

AS

DECLARE @sqlselect nvarchar(4000),
@sqlwhere nvarchar(4000),
@strsql nvarchar(2000)

--SELECT @sqlselect = 'SELECT cnid,progno,projno,contractno,cntitle FROM TAB_ccsNetCN'
SELECT progno, projno, contractno
FROM TAB_ccsNetCN ( NOLOCK )
WHERE ProgNO = COALESCE(@ProgNO, ProgNo)
AND ProjNO = COALESCE(@ProjNO, ProjNO)
AND ContractNO = COALESCE(@ContractNO, ContractNO)

GO

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-24 : 11:11:04

As always, keep it simple. do not overcomplicate or confuse yourself. Look at the following very carefully:


SELECT 
  progno, projno, contractno
FROM 
  TAB_ccsNetCN
WHERE 
  (@ProgNo is null OR ProgNO = @ProgNO) AND
  (@ProjNo is null OR ProjNO = @ProjNO) AND
  (@ContractNO is null OR ContractNO = @ContractNO)

Does that make sense?

- Jeff

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-24 : 11:16:47

Jeff,

You can lead a horse to water....

http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=753974
http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=754652

reddymade
Posting Yak Master

165 Posts

Posted - 2004-11-24 : 11:35:45

Jeff,
I really appreciate your help, It is working good.
Thank you very much.

Ehorn,
I am totally new to the stored procedures. i am just starting to learn Stored proc's.
you really have a problem instead of giving me a solution, you make your allegations on the other person in www.asp.net, who atleast are trying to provide me a solution.

And here also i see you are attaching the thread for that post.
I don't really think you are professional.
you proved to be a problem there and also here you are again proving your self to be a problem.

quote:
Originally posted by jsmith8858

As always, keep it simple. do not overcomplicate or confuse yourself. Look at the following very carefully:

SELECT
progno, projno, contractno
FROM
TAB_ccsNetCN
WHERE
(@ProgNo is null OR ProgNO = @ProgNO) AND
(@ProjNo is null OR ProjNO = @ProjNO) AND
(@ContractNO is null OR ContractNO = @ContractNO)

Does that make sense?

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-24 : 11:48:57

quote:
Originally posted by reddymade

Jeff,
I really appreciate your help, It is working good.
Thank you very much.

Ehorn,
I am totally new to the stored procedures. i am just starting to learn Stored proc's.
you really have a problem instead of giving me a solution, you make your allegations on the other person in www.asp.net, who atleast are trying to provide me a solution.

And here also i see you are attaching the thread for that post.
I don't really think you are professional.
you proved to be a problem there and also here you are again proving your self to be a problem.

Huh ???? He gave you the EXACT SAME SOLUTION I just gave you. Literally, almost character for character. Why do you thank me and insult him? How did he prove himself to be a "problem" ?? Why did you ignore his advice and ask elsewhere?

- Jeff

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-25 : 02:01:40

keep it cool ehorn, some people are really good at accepting suggestions/ideas, some simply aren't.

maybe reddymade REALLY WANTED/NEEDED to use dsql, for whatever reason. atleast, he got an answer.

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources