Write a Script as Varchar Parameter in Stored Procedure

[eflatunn]

Hi all,

I paid 3 hours for it but couldn't get succeed.
I'm writing a stored procedure to get a data entry in a table. The problem is that I can't simply use commands "SELECT" or "CASE" directy. First I have to declare a varchar parameter and put all of this in this parameter. (The reason is that the stored procedure is not unique for a table. Year and month, which are parts of the table name are also parameters of my S.P.)
There is NO problem when I don't use "CASE" . e.g.

CREATE PROCEDURE [GetCustomerInfo]
(@Year_1 [char](4),
@Month_2 [char](2),
@CustomerId_3 [int])

AS
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[Customers_'+@Year_1+'_'+@Month_2+']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
DECLARE @Sql varchar(300)
SET @Sql='
SELECT
[CustomerId],
[CustomerName],
FROM [Customers_'+@Year_1+'_'+@Month_2+']
WHERE
@CustomerId_3='+CAST(@DetectorId_3 AS VARCHAR(10))+' '
EXEC(@Sql)
END

The SP above works.
But when I use a CASE statement to while "select"ing a column, the SP doesn't work properly although there is no syntax error. I still think that the problem is due to syntax.
How can I come along with this? Please help me.

[almaz]

Replace "DECLARE @Sql varchar(300)" with "DECLARE @Sql varchar(4000)".

If it won't help, please post the script you are trying to run (with CASE statement)

[eflatunn]

Replace "DECLARE @Sql varchar(300)" with "DECLARE @Sql varchar(4000)".

If it won't help, please post the script you are trying to run (with CASE statement)

Thank you for your advice. I already tried this but it didn't help. But my problem is solved:) (I don't know how) I just wrote the SProc all over from the beginning.