SQL Query

Question

hi,

I Have a small SP

CREATE PROCEDURE [SP_GetAll]

@SearchType varchar(20)

AS

BEGIN

SELECT COUNT(*) FROM WebSalesLine WHERE @SearchType LIKE '%WSH1076%'

END

SearchType is the Parameter where i send ID,Name

when i send both of these the query shows like

SELECT COUNT(*) FROM WebSalesLine WHERE '@SearchType' LIKE '%WSH1076%'

i.e. we get single quotes to @SearchType as it datatype is varchar

i want to remove these quotes.

pls help

Regards

Kishan

Answer 1

you have to use dynamice query for this case

 

CREATE PROCEDURE [SP_GetAll]
@SearchType varchar(20)
AS
BEGIN
declare @q nvarchar(1000)
set @q = 'SELECT COUNT(*) FROM WebSalesLine WHERE '+@SearchType+' LIKE ''%WSH1076%'''
execute sp_executesql @q

END

 

---

Best Regards,
Melissa Suciadi

If you have found this post helpful, please click the 'Vote as Helpful ' link (the green triangle and number on the top-left).

If this post answers your question, click the 'Mark As Answered ' link below. It helps others who experience the same issue in future to find the solution.

Answer 2

SELECT COUNT(*) FROM WebSalesLine WHERE '@SearchType' LIKE '%WSH1076%'

You need the column in the WHERE clause like:

SELECT COUNT(*) FROM WebSalesLine WHERE YourColumn LIKE @SearchType 

If @SearchType is the column, then you need to use dynamic query:

http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

 Dynamic SQL video: http://www.youtube.com/watch?v=tG1zv7liLXY

 

---

Kalman Toth, SQL Server & BI Training, SSAS OLAP, SSIS, SSRS; http://www.SQLUSA.com