Help with Dynamic Search as stored procedure

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 2005 Forums

Transact-SQL (2005)

Help with Dynamic Search as stored procedure

Author

Topic

ggr_fi
Starting Member

3 Posts

Posted - 2010-06-04 : 07:57:35

I would like to implement this stored procedure but i got error (im new to T-SQL Programming )

CREATE PROCEDURE sp_SearchContacts
@LastName VarChar ,
@Firstname VarChar ,
@testing VarChar(100)= 'SELECT *FROM Contacts '
AS
BEGIN
DECLARE @SQL VarChar(100) ;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if (@Lastname <> '')
BEGIN

@SQL = @testing + ' where LastName = ' + @LastName ;
@SQL ;
END
else
BEGIN
@testing ;

END

I would like to output Contacts based on the user Input
Thank you in Advance
Simon

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-04 : 08:18:44

[code]
CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
DECLARE @SQL VarChar(100) ;
DECLARE @testing VarChar(100)
SET @Testing = 'SELECT *FROM Contacts '
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if (@Lastname <> '')
BEGIN

SET @SQL = @testing + ' where LastName = ' + @LastName ;
SELECT @SQL ;
END
else
BEGIN
SELECT @testing ;

END

END
[/code]

Also, don't name your procedures with sp_, sp_ is what SQL Server uses for its procedures. Use spSearchContacts or somthing else than sp_

Jim

Everyday I learn something that somebody else already knew

ggr_fi
Starting Member

3 Posts

Posted - 2010-06-04 : 09:06:08

Thank you for your reply

I need stored procedure wich return data not SQL statement .
This return sql Statement :
CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
DECLARE @SQL VarChar(100) ;
DECLARE @testing VarChar(100)
SET @Testing = 'SELECT *FROM Contacts '
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
if (@Lastname <> '')
BEGIN

SET @SQL = @testing + ' where LastName = ' + @LastName ;
this return sql not data , i need to return data from Conatcts table:
SELECT @SQL ;

END
else
BEGIN
SELECT @testing ;

END

END

Thank you

Sachin.Nand

2937 Posts

Posted - 2010-06-04 : 09:15:10

use EXEC(@SQL)

"Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless."
PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 09:19:36

Can't you do this?

CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Contacts where (LastName = @LastName or @LastName is null);
END

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-04 : 09:54:51

quote:
Originally posted by madhivanan

Can't you do this?

CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Contacts where (LastName = @LastName or @LastName is null);
END

Madhivanan

Failing to plan is Planning to fail

This is not OP's requirement
if lastname is supplied '' then show all the data

this might be solution


CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20) 
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(500)
SET @SQL = 'SELECT * FROM Contacts ' + CASE WHEN @LastName = '' OR @LastName IS NULL THEN '' ELSE 'WHERE LastName = @LastName' END
EXEC ( @SQL )
END

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-04 : 10:05:09

quote:
Originally posted by vaibhavktiwari83

quote:
Originally posted by madhivanan

Can't you do this?

CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Contacts where (LastName = @LastName or @LastName is null);
END

Madhivanan

Failing to plan is Planning to fail

This is not OP's requirement
if lastname is supplied '' then show all the data

this might be solution

CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(500)
SET @SQL = 'SELECT * FROM Contacts ' + CASE WHEN @LastName = '' OR @LastName IS NULL THEN '' ELSE 'WHERE LastName = @LastName' END
EXEC ( @SQL )
END

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Then it can be

CREATE PROCEDURE sp_SearchContacts
(
@LastName VarChar (20),
@Firstname VarChar (20)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Contacts where (LastName = @LastName or @LastName ='' or @LastName is null);
END

Madhivanan

Failing to plan is Planning to fail

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-04 : 10:38:26

Madhi,
Sorry i misread LastName IS null instead of @LastName IS NULL

thats why i created dynamic query for the same.

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources