sql procedure where i have a subquery in an insert statement 1

[G00GLER]

SET @MaxId = SELECT MAX(id) FROM @tblName
DOESN'T work , i need to return maxid of a tablename that is a parameter

CREATE PROCEDURE dbo.sp_SubmitRequest2 ( 
	@tblName, 
	@Deadline varchar(50), 
	@Requested varchar(50), 
	@RequestedBy varchar(50), 
	@RequestedFor varchar(50), 
	@CAM varchar(50), 
	@CAMNAME varchar(50)
) AS

DECLARE  @MaxId int

SET @MaxId = SELECT MAX(id) FROM @tblName

INSERT INTO insiteCROMO.dbo._bTickets (theTable,theTableId,Deadline,Requested,RequestedBy,RequestedFor,CAM,CAMNAME) VALUES (@tblName,@MaxId,@Deadline,@Requested,@RequestedBy,@RequestedFor,@CAM,@CAMNAME)
GO

 

[SQLDenis]

Untested, also you forgot the data type for @tblName

CREATE PROCEDURE dbo.sp_SubmitRequest2 ( 
    @tblName [b]varchar(50)[/b], 
    @Deadline varchar(50), 
    @Requested varchar(50), 
    @RequestedBy varchar(50), 
    @RequestedFor varchar(50), 
    @CAM varchar(50), 
    @CAMNAME varchar(50)
) AS

SET NOCOUNT ON
DECLARE  @MaxId int
DECLARE @chvSQL NVARCHAR(100)


SELECT @chvSQL = N'SELECT @MaxId = MAX(id) FROM ' + @tblName
EXEC sp_executesql @chvSQL, N'@MaxId INT OUTPUT', @MaxId OUTPUT


INSERT INTO insiteCROMO.dbo._bTickets (theTable,theTableId,Deadline,Requested,RequestedBy,RequestedFor,CAM,CAMNAME) VALUES (@tblName,@MaxId,@Deadline,@Requested,@RequestedBy,@RequestedFor,@CAM,@CAMNAME)
SET NOCOUNT OFF

GO

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[bikerboy718]

have you tried this

SELECT @MaxId = MAX(id) FROM @tblName

With Great Power Comes Great Responsibility!!!

Michael

 

[G00GLER]

bikerboy yes i tried that.

Denis YOU ARE AWESOME definitely reading your blog now. Originally I defined @tblName as varchar but the whole return value of query was throwing me off.

THIS WAS SO HELPFUL

 

[SQLDenis]

Thanks, how is IE7 working for you?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[G00GLER]

good. have you had problems with it?

I had one development issue and called their tech support and actually got a team member call me back twice to follow up.