CODEWITHSUNDEEP
sql-server-2005t-sqlstored-procedures
Jorge
Jorge

Reputation: 18237

where is the problem

I new transact sql i'm stock with this

CREATE PROCEDURE Example 
    @MAESTRDESC varchar(50) --Contiene el valor a ingresar por pantalla
AS
/****Vars*****/
DECLARE
    @Result TABLE
    (
        ClientesDisponibles int,
        ClientesAgendados   int
    )
DECLARE
    @SQL varchar(500),--Guarda la cantidad de clientes disponibles
    @SQLAGENDADOS varchar(500)

SET @SQL = 'SELECT COUNT(idtable) from table';
SET @SQL = 'SELECT COUNT(idtable2) from table2';

INSERT INTO @Result EXEC @SQL, EXEC@SQLAGENDADOS;

SELECT 
    R.ClientesDisponibles,
    R.ClientesAgendados 
FROM 
    @Result R
RETURN

EDIT the exception say's

Column name or number of supplied values does not match table definition.

give's and exception but i don't have any idea about what i'm doing wrong. Thanks

Upvotes: 0

Views: 107

Answers (3)

Neil Knight
Neil Knight

Reputation: 48537

It won't work because of this line:

DECLARE @SQL int

You need to declare @SQL as a VARCHAR in order to use it as a string. The same goes for @SQLAGENDADOS.

However, you should also see @Martins answer regarding chaining multiple executes.

Why don't you do the following:

DECLARE @Count1 INT;
DECLARE @Count2 INT;

SELECT @Count1 = COUNT(idtable) 
  FROM table;

SELECT @Count2 = COUNT(idtable2) 
  FROM table2;

INSERT INTO @Result 
     VALUES (@Count1, @Count2);

Upvotes: 2

Ocaso Protal
Ocaso Protal

Reputation: 20237

CREATE PROCEDURE Example 
@MAESTRDESC varchar(50) --Contiene el valor a ingresar por pantalla
    AS
/****Vars*****/
DECLARE
    @Result TABLE
    (
        ClientesDisponibles int,
        ClientesAgendados   int
    )
DECLARE
    @SQL int,--Guarda la cantidad de clientes disponibles
    @SQLAGENDADOS int

SELECT @SQL = COUNT(idtable) from [table]
SELECT @SQLAGENDADOS = COUNT(idtable2) from [table2]

INSERT @Result VALUES (@SQL, @SQLAGENDADOS)

SELECT 
    R.ClientesDisponibles,
    R.ClientesAgendados 
FROM 
    @Result R
RETURN

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453018

You can't chain together multiple EXEC calls like that and you are missing parentheses (and Neil's point too!)

You could do 

INSERT INTO @Result(ClientesDisponibles) 
EXEC (@SQL)

INSERT INTO @Result(ClientesAgendados) 
EXEC (@SQLAGENDADOS)

This will insert 2 separate rows though. No idea why you aren't using sp_executesql for this as per your previous question.

Upvotes: 2

Related Questions