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 2008 Forums
 Transact-SQL (2008)
 Stored Procedure

Author  Topic 

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-04 : 15:24:08
I'm trying to create a stored procedure that will search information in a view and return a result based on the columns in the view.

CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(200))
AS
Declare @tName nvarchar(200)=('%'+@Talisma_Search% + '%')
Declare @tSSN nvarchar(200)=('%'+@Talisma_Search% + '%')
Declare @tEmail nvarchar(200)=('%'+@Talisma_Search% + '%')
SELECT
[dbo].[tblTalisma_Lookup].tName,
[dbo].[tblTalisma_Lookup].tSSN,
[dbo].[tblTalisma_Lookup].tEmail
FROM [dbo].[tblTalisma_Lookup]
WHERE tName=@tName OR tSSN=@tSSN Or tEmail=@tEmail
ORDER BY tName

I read the following article to come up with some of the query.
http://www.sqlteam.com/article/stored-procedures-returning-data

Any Help is Greatly appreciated.

BruceT
Yak Posting Veteran

78 Posts
Posted - 2011-11-04 : 15:38:39
Try searching for dynamic sql
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-05 : 13:23:11
why not use parameter directly?


CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(200))
AS
Declare @tName nvarchar(200)=('%'+@Talisma_Search% + '%')
Declare @tSSN nvarchar(200)=('%'+@Talisma_Search% + '%')
Declare @tEmail nvarchar(200)=('%'+@Talisma_Search% + '%')
SELECT 
[dbo].[tblTalisma_Lookup].tName,
[dbo].[tblTalisma_Lookup].tSSN,
[dbo].[tblTalisma_Lookup].tEmail
FROM [dbo].[tblTalisma_Lookup]
WHERE tName='%'+@Talisma_Search + '%' OR tSSN='%'+@Talisma_Search + '%' Or tEmail='%'+@Talisma_Search + '%'
ORDER BY tName


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-11-05 : 14:32:55
Hi I think you might just be a little confused and not asking the right question.

By doing this:

Declare @tName nvarchar(200)=('%'+@Talisma_Search% + '%')
Declare @tSSN nvarchar(200)=('%'+@Talisma_Search% + '%')
Declare @tEmail nvarchar(200)=('%'+@Talisma_Search% + '%')

You are adding the wildcard '%' to the start and end of your string. But then you use a predicate like this

WHERE tName=@tName OR tSSN=@tSSN Or tEmail=@tEmail

Which looks for the *exect* match. For example it will only bring back rows where either of those columns is EXECTLY equal to '%' + @variable + '%'

I suspect what you wanted was to look inside each entry and bring back any rows where there is a pattern match on your input parameter?

If that is what you want to do then you can do this:
 
CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(200))
AS
DECLARE @searchTerm NVARCHAR(202) = '%'+@Talisma_Search% + '%'
SELECT
    tl.[tName]
    , tl.[tSSN]
    , tl.[tEmail]
FROM
    [dbo].[tblTalisma_Lookup] AS tl
WHERE
    tl.[tName] LIKE @searchTerm
    OR t1.[tSSN] LIKE @searchTerm
    OR t1.[tEmail] LIKE @serachTerm
ORDER BY
    tl.[tName]

Here I've added the percent signs once (because you are looking for the same thing in each column). You could also do as Visakh suggested and don't use any kind of intermediate variable.

The big difference is that I'm using LIKE rather than = as my predicate. I suspect that's what you were really wanting to do.

Don't use dynamic sql for this. It wouldn't help at all.

One thing to be aware of is that you are performing a table scan here. There is no index that you could use to help the query.

If you need to do something like this on a big dataset then you should probably look into FULLTEXT indexing but I doubt that you'll need that if you are asking these kind of questions.

Good Luck

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 08:59:13
Thank you both for the response. It appears that now I'm getting a message when I run the query:

CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(255))
AS
DECLARE @searchTerm NVARCHAR(255) = '%'+@Talisma_Search% + '%'
SELECT
[dbo].[tblTalisma_Lookup].[tName]
, [dbo].[tblTalisma_Lookup].[tSSN]
, [dbo].[tblTalisma_Lookup].[tEmail]
FROM
[dbo].[tblTalisma_Lookup]
WHERE
[dbo].[tblTalisma_Lookup].[tName] LIKE @searchTerm
OR [dbo].[tblTalisma_Lookup].[tSSN] LIKE @searchTerm
OR [dbo].[tblTalisma_Lookup].[tEmail] LIKE @searchTerm
ORDER BY
[dbo].[tblTalisma_Lookup].[tName]

"The data types nvarchar and varchar are incompatible in the modulo operator."
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-07 : 09:25:19
no need of intermediate variable. use like


CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(255))
AS
SELECT
[dbo].[tblTalisma_Lookup].[tName]
, [dbo].[tblTalisma_Lookup].[tSSN]
, [dbo].[tblTalisma_Lookup].[tEmail]
FROM
[dbo].[tblTalisma_Lookup]
WHERE
[dbo].[tblTalisma_Lookup].[tName] LIKE  '%'+@Talisma_Search% + '%'
OR [dbo].[tblTalisma_Lookup].[tSSN] LIKE  '%'+@Talisma_Search% + '%'
OR [dbo].[tblTalisma_Lookup].[tEmail] LIKE  '%'+@Talisma_Search% + '%'
ORDER BY
[dbo].[tblTalisma_Lookup].[tName]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

jassi.singh
Posting Yak Master

122 Posts
Posted - 2011-11-07 : 09:50:15
ALTER PROCEDURE [GetAll]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN


SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from temptable'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END


Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 09:53:49
That wast he solution. Thank you very much for all of the help!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-07 : 09:54:58
quote:
Originally posted by jassi.singh

ALTER PROCEDURE [GetAll]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN


SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from temptable'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END


Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh


Finally some useful suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 10:54:44
In the opening sequence of

ALTER PROCEDURE [GetAll]

Will this alter the procedure of all the stored procs in the DataBase? Can I replace the [GetAll] with my specific stored proc [tblTalisma_lookup]?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-07 : 10:57:28
quote:
Originally posted by meberg66219

In the opening sequence of

ALTER PROCEDURE [GetAll]

Will this alter the procedure of all the stored procs in the DataBase? Can I replace the [GetAll] with my specific stored proc [tblTalisma_lookup]?



it will alter the procedure GetAll which is already present in db
if you need to replace it with other procedure you need to first drop this and then create the other procedure

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 11:32:09
I executed this by dropping and creating the procedure.

ALTER PROCEDURE [dbo].[Talisma_Search]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN


SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from [dbo].[Talisma_Lookup]'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END
GO
Then I created the following to execute the query.

Now I'm attempting to retrieve some data using the following:

EXEC dbo.Talisma_Search @Talisma_Search='Smith'
or
EXEC dbo.talisma_search @tname='smith'
or
EXEC dbo.talisma_search *='smith'

All of these result in a syntax error message. How can I execute a statement that will return based on part or all of a name, SSN or Email?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2011-11-07 : 11:40:46
quote:
Originally posted by meberg66219

I executed this by dropping and creating the procedure.

ALTER PROCEDURE [dbo].[Talisma_Search]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN


SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from [dbo].[Talisma_Lookup]'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END
GO
Then I created the following to execute the query.

Now I'm attempting to retrieve some data using the following:

EXEC dbo.Talisma_Search @Talisma_Search='Smith'
or
EXEC dbo.talisma_search @tname='smith'
or
EXEC dbo.talisma_search *='smith'

All of these result in a syntax error message. How can I execute a statement that will return based on part or all of a name, SSN or Email?

it should be executed as

EXEC dbo.Talisma_Search @SearchParam='Smith'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 13:13:54
Thank you so much. However now I get a message:

An expression of non-boolean type specified in a context where a condition is expected, near 'Smith'
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-11-07 : 15:33:15
quote:
Originally posted by visakh16

quote:
Originally posted by meberg66219

I executed this by dropping and creating the procedure.

ALTER PROCEDURE [dbo].[Talisma_Search]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN


SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from [dbo].[Talisma_Lookup]'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END
GO
Then I created the following to execute the query.

Now I'm attempting to retrieve some data using the following:

EXEC dbo.Talisma_Search @Talisma_Search='Smith'
or
EXEC dbo.talisma_search @tname='smith'
or
EXEC dbo.talisma_search *='smith'

All of these result in a syntax error message. How can I execute a statement that will return based on part or all of a name, SSN or Email?

it should be executed as

EXEC dbo.Talisma_Search @SearchParam='Smith'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/




What?

Has?

Happened?

Here?

Visakh Seriously. You agree with this? this dangerous nonsence?

meberg66219 -- this is a seriously bad idea
1) It doesn't help answer your question. I don't understand why you think it does. If you call it as Visakh suggests then the sql that will call will be:

EXEC dbo.Talisma_Search @SearchParam='Smith'


This would generate the sql code:

SELECT * from [dbo].[Talisma_Lookup] WHERE Smith


Which wouldn't work at all smith isn't a predicate. In fact that's why you are getting the message:

An expression of non-boolean type specified in a context where a condition is expected, near 'Smith'

Also -- this procedure is insanely dangerous

Ig you were to call it with the parameter '1 = 1; DROP TABLE Sales; --'
Guess what,
1) You'd get all the rows back because every row satisfy's the predicate 1 = 1
2) You'd loose everything in the sales table. (and you wouldn't know)

THIS IS A STUPID, STUPID idea.

Sorry jassi.singh -- you shouldn't be posting this garbage
Visakh... WHAT? Did you read this? I'm surprised.

Sorry but this really annoys me.

Charlie.



Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 15:44:49
Charlie,

I am using a view that I created that is joining information from 3 tables. I just checked and view is intact with the proper information.

If I go back to the original idea:

CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(255))
AS
SELECT
[dbo].[tblTalisma_Lookup].[tName]
, [dbo].[tblTalisma_Lookup].[tSSN]
, [dbo].[tblTalisma_Lookup].[tEmail]
FROM
[dbo].[tblTalisma_Lookup]
WHERE
[dbo].[tblTalisma_Lookup].[tName] LIKE '%'+@Talisma_Search% + '%'
OR [dbo].[tblTalisma_Lookup].[tSSN] LIKE '%'+@Talisma_Search% + '%'
OR [dbo].[tblTalisma_Lookup].[tEmail] LIKE '%'+@Talisma_Search% + '%'
ORDER BY
[dbo].[tblTalisma_Lookup].[tName]

I get the error The data types nvarchar and varchar are incompatible in the modulo operator.

I'm attempting to research this issue but I still don't have a viable solution.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-11-07 : 15:50:00
I see what you have done. I will mark what you need to delete in red:

CREATE PROCEDURE dbo.Talisma_Search
(@Talisma_Search NVARCHAR(255))
AS
SELECT
[dbo].[tblTalisma_Lookup].[tName]
, [dbo].[tblTalisma_Lookup].[tSSN]
, [dbo].[tblTalisma_Lookup].[tEmail]
FROM
[dbo].[tblTalisma_Lookup]
WHERE
[dbo].[tblTalisma_Lookup].[tName] LIKE '%'+@Talisma_Search% + '%'
OR [dbo].[tblTalisma_Lookup].[tSSN] LIKE '%'+@Talisma_Search% + '%'
OR [dbo].[tblTalisma_Lookup].[tEmail] LIKE '%'+@Talisma_Search% + '%'
ORDER BY
[dbo].[tblTalisma_Lookup].[tName]

Just delete the redundant % signs.

Typo? or a takeover from some other language's syntax?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-11-07 : 15:55:05
quote:
Originally posted by jassi.singh

ALTER PROCEDURE [GetAll]
(
@SearchParam NVARCHAR(4000) = ''
)
AS
BEGIN


SET NOCOUNT ON
DECLARE @SqlString NVARCHAR(4000)

SET @SqlString = 'SELECT * from temptable'

IF LTRIM ( RTRIM ( @SearchParam ) ) <> ''
BEGIN
SET @SqlString = @SqlString + ' WHERE ' + @SearchParam
END

EXECUTE sp_executesql @SqlString
END


Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh


Don't post dangerous garbage like this. It doesn't do anything to help and it's a security hole waiting to happen.

Why?
http://xkcd.com/327/

Please, please, read this link. If you actually read it and understand then you will be a much better sql programmer for it.
http://www.sommarskog.se/dynamic_sql.html

Sorry to call you out but this is seriously bad advice.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

meberg66219
Yak Posting Veteran

65 Posts
Posted - 2011-11-07 : 16:04:02
I had done a group of searches on this stored procedure so it was a left over from a previous version.

Thank you very much for the help Charlie!

One more quick question:

EXEC dbo.talisma_Search @Talisma_Search = 'smith'

I get
Procedure or function 'Talisma_Search' expects '@Talsma_Search', which was not supplied.

I'm pretty new to this and I can't seem to see what I'm doing wrong. I tried variations, but received the same result.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-11-07 : 16:06:59
typo --

The stored proc expects @Talsma_search but you are passing it @Talisma_Search

Just use the right parameter name!

your welcome

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts
Posted - 2011-11-07 : 16:08:45
or it's a typo when you made the stored proc.

Regardless the parameter is called @Talsma_Search and you are stating you are allocating 'Smith' to "Talisma_Search.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site