Why it takes my value as column name

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)

Why it takes my value as column name

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-09-07 : 09:27:23

Hello,

I have SP as below

When i try to execute it gives me error like

CREATE PROCEDURE [dbo].[ERS_SP_SearchOffer]
-- Add the parameters for the stored procedure here
@SearchType nvarchar(2),
@SearchText nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ExecQuery nvarchar(100)
BEGIN
Select @ExecQuery = 'SELECT * FROM ERS_NewOfferOperaion
WHERE OfferType=' + @SearchType +
' AND OfferNo=' + @SearchText
exec (@ExecQuery)
END
END

When I execute it like as below

EXEC ERS_SP_SearchOffer '0','KSIEJ040'

it gives me error like

Msg 207, Level 16, State 1, Line 1
Invalid column name 'KSIEJ040'

why it take value as column name

Please help me
Regards,

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-07 : 09:39:35

The main code should be

Select @ExecQuery = 'SELECT * FROM ERS_NewOfferOperaion
WHERE OfferType=' + @SearchType +
' AND OfferNo=''' + @SearchText+''''

Also, I dont see any point in using dynamic sql in this case

It can be simplified to

CREATE PROCEDURE [dbo].[ERS_SP_SearchOffer]
-- Add the parameters for the stored procedure here
@SearchType nvarchar(2),
@SearchText nvarchar(100)
AS
BEGIN
SET NOCOUNT ON;
BEGIN
SELECT * FROM ERS_NewOfferOperaion
WHERE OfferType=@SearchType
AND OfferNo= @SearchText
END
END

Madhivanan

Failing to plan is Planning to fail

asifbhura
Posting Yak Master

165 Posts

Posted - 2010-09-07 : 09:45:52

thanks it is working

but i have columnname and value both are dynamically passing

How to achieve this as below i tried

ALTER PROCEDURE [dbo].[ERS_SP_SearchOfferByDate]
-- Add the parameters for the stored procedure here
@SearchOption nvarchar(50),
@SearchType nvarchar(2),
@StartDate nvarchar(100),
@EndDate nvarchar(15)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
DECLARE @ExecQuery nvarchar(100)
SET @ExecQuery=@SearchOption
-- Insert statements for procedure here
if @EndDate <> ''
BEGIN
select @ExecQuery = 'SELECT * FROM ERS_NewOfferOperation WHERE '
+ @SearchOption +'>=' + @StartDate + ' AND' +
@SearchOption +'<=' + @EndDate +
' AND OfferType=' + @SearchType

Here @SearchOption can be startdate,enddate or RegDate

please how to achieve this

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 09:50:18

NO DYNAMIC SQL!

IF @SearchOption = 'startdate'
	SELECT	*
	FROM	ERS_NewOfferOperation
	WHERE	startDate >= @StartDate
		AND startDate <= @EndDate
		AND OfferType = @SearchType
ELSE IF @SearchOption = 'enddate'
	SELECT	*
	FROM	ERS_NewOfferOperation
	WHERE	enddate >= @StartDate
		AND enddate <= @EndDate
		AND OfferType = @SearchType
ELSE IF @SearchOption = 'RegDate'
	SELECT	*
	FROM	ERS_NewOfferOperation
	WHERE	RegDate >= @StartDate
		AND RegDate <= @EndDate
		AND OfferType = @SearchType

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-07 : 09:51:27

Or, if you want a better execution plan

SELECT	*
FROM	ERS_NewOfferOperation
WHERE	startDate >= @StartDate
	AND startDate <= @EndDate
	AND OfferType = @SearchType
	AND @SearchOption = 'startdate'

UNION ALL

SELECT	*
FROM	ERS_NewOfferOperation
WHERE	enddate >= @StartDate
	AND enddate <= @EndDate
	AND OfferType = @SearchType
	AND @SearchOption = 'enddate'

UNION ALL

SELECT	*
FROM	ERS_NewOfferOperation
WHERE	RegDate >= @StartDate
	AND RegDate <= @EndDate
	AND OfferType = @SearchType
	AND @SearchOption = 'RegDate'

N 56°04'39.26"
E 12°55'05.63"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-09-07 : 10:14:37


SELECT	*
FROM	ERS_NewOfferOperation
WHERE	
(	
	startDate >= @StartDate
	AND startDate <= @EndDate
	AND OfferType = @SearchType
	AND @SearchOption = 'startdate'
)
OR
(
	enddate >= @StartDate
	AND enddate <= @EndDate
	AND OfferType = @SearchType
	AND @SearchOption = 'enddate'
)
OR
(
	RegDate >= @StartDate
	AND RegDate <= @EndDate
	AND OfferType = @SearchType
	AND @SearchOption = 'RegDate'	
)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources