stored procedure error.

your varchar doesn't have a capacity

varchar(max)

please excuse entry...I looked over your question too fast without truly reading it...

Sorry here is the varchar(max).

But I am still getting the same error.

CREATE PROCEDURE dbo.goto_row_number
(@table_schema_and_name varchar(max))
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
select *
  from (select *,row_number() over (order by (select 1)) rn from @table_schema_and_name) t1
 where rn = 1
END
GO
                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

in the past I have created procedures such as

Create procedure [dbo].[splist_job_by_sessionID]
(@sessionID int)
as
select * from sys.dm_exec_sessions where session_id=@sessionID

and this works.

Try removing the brackets around the variable;

CREATE PROCEDURE dbo.goto_row_number
@table_schema_and_name varchar(max)
AS
BEGIN
.....

and you also may not need the (max)

Hi Mr_Shaw,

Use dynamic sql.

DECLARE @sql varchar(200)
@sql='select *
  from (select *,row_number() over (order by (select 1)) rn from ' + @table_schema_and_name) ' t1
 where rn = 1'
execute_sql @sql

If I am right, it will  be something like this.

--Raj

Hi Mr_Shaw,

Use dynamic sql.

DECLARE @sql varchar(200)
@sql='select *
  from (select *,row_number() over (order by (select 1)) rn from ' + @table_schema_and_name) ' t1
 where rn = 1'
execute_sql @sql

If I am right, it will  be something like this.

--Raj

Ok this seems to pass the syntax check, I've used single quotes on the variable rn and put the SQL query within the EXEC(' ') part

CREATE PROCEDURE dbo.goto_row_number
@table_schema_and_name varchar
AS
EXEC('
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
select *
  from (select *,row_number() over (order by (select 1)) rn from @table_schema_and_name) t1
 where rn = ''1''
')
GO

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Oh. Sorry for the duplication. I am traveling in train and due to some technical problems with signal, it happened.

--Raj

There is a correction in my sql

DECLARE @sql varchar(200)
@sql='select *
  from (select *,row_number() over (order by (select 1)) rn from ' + @table_schema_and_name + ') t1
 where rn = 1'
execute_sql @sql

--Raj

I don't understand why this query needs to be dynamic.

I have created lot of stored procedures in the past which use parameters and I did not need them to be dynamic. For example:

Create procedure [dbo].[splist_job_by_sessionID]
(@sessionID int)
as
select * from sys.dm_exec_sessions where session_id=@sessionID

angelIII why is that?

I think you might just be missing the single quotation marks on the rn = 1 section. Try adding them as per my code snippet above perhaps?

You have to use Execute command

Create PROCEDURE dbo.goto_row_number
(@table_schema_and_name varchar(max))
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
Execute('select *
  from (select *,row_number() over (order by (select 1)) rn from '+ @table_schema_and_name +') t1
 where rn = 1')
END
GO

The above won't work, this is the corrected version. You need to keep a check on the single quotes when using stored procedures, and the (max) is not needed(it fails the syntax check otherwise);

Create PROCEDURE dbo.goto_row_number
(@table_schema_and_name varchar)
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
 
    -- Insert statements for procedure here
Execute('
select *
from (select *,row_number() over (order by (select 1)) rn from '+ @table_schema_and_name +') t1
where rn = ''1''
')
END
GO

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Shaw,

Use EXECUTE command to execute dynamic sql. Previously I guessed it wrongly.

--Raj

Execute('select *
  from (select *,row_number() over (order by (select 1)) rn from ' + @table_schema_and_name + ') t1
 where rn = 1')
                                              

1:
2:
3:
4:

Modify that
'ALTER PROCEDURE sp_NonDynamic' part
to
'CREATE PROCEDURE sp_NonDynamic'

--Raj