Dynamic SQL Problem - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Dynamic SQL Problem

Author

Topic

Mindhunter74
Starting Member

2 Posts

Posted - 2009-06-06 : 14:02:14

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Proc [dbo].[DSelection]
@company_id int
as
declare @SQL nvarchar(1000)

SELECT @SQL =
'SELECT bus.id
FROM bus
WHERE ( bus.company_id = ' + @company_id + ')'
exec (@SQL)

When I execute the above stored procedure with the following:

USE [Reservation]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[DSelection]
@company_id =2
SELECT 'Return Value' = @return_value
GO

I get the following error:
Msg 245, Level 16, State 1, Procedure DSelection, Line 7
Conversion failed when converting the varchar value 'SELECT bus.id
FROM bus
WHERE ( bus.company_id = ' to data type int.

Could you please help me with this problem?

Thank you.

Mindhunter74
Starting Member

2 Posts

Posted - 2009-06-06 : 14:09:42

I got it.
Just used convert(nvarchar, @company_id) instead of @company_id

X002548
Not Just a Number

15586 Posts

Posted - 2009-06-06 : 20:04:09

I got it

Don't do this

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-07 : 03:00:51