Hello, I am looking for at this newsgroup to find solution for:

in stored procedure:

i can concenate strings to one string:

DECLARE @SQL NVARCHAR(4000)

SET @SQL = 'INSERT INTO.....' + @SQL1 + '....' + @SQL2.... etc.

the length of @SQL must have about 1000 characters, but it truncated to
130 characters.


i found a solution>
I change in QA Tools / Options / Results / Maximum characters per
column to 8000 but nothing happend.


using: SQL server w2k 8.00.194

Please help.
Kolby

Can you post a reproduction of the problem, something we can execute that show this behavior?

Show quoteHide quote

Thanks for your interest, here is my SP:

CREATE PROCEDURE PohybKDatu
  ( @CSKLADU INT, @CMATERIALU VARCHAR(30),
    @ODDATA DATETIME, @DODATA DATETIME,
    @ODHLAADR VARCHAR(15), @DOHLAADR VARCHAR(15),
    @ODDOKLPOH VARCHAR(8), @DODOKLPOH VARCHAR(8),
    @ODADRESY VARCHAR(15), @DOADRESY VARCHAR(15) )
AS
BEGIN
  DECLARE @SQL  NVARCHAR(4000)
  DECLARE @SQL2 NVARCHAR(1000)
  DECLARE @INP  NVARCHAR(1000)
  DECLARE @OUP  NVARCHAR(1000)


  SET @SQL2 = N' FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU'
  IF @ODDATA IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND DATUM>=@iODDATA' +
                        N' AND DATUM<=@iDODATA'
  ELSE
    SET @SQL2 = @SQL2 + N' AND DATUM<@iDODATA'
  SET @INP = N'@iCSKLADU INT, @iCMATERIALU VARCHAR(30), @iODDATA
DATETIME, @iDODATA DATETIME'
  SET @OUP = N'@CSKLADU, @CMATERIALU, @ODDATA, @DODATA'

  IF @ODDOKLPOH IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)>=UPPER(@iODDOKLPOH)'
    SET @INP = @INP + N',@iODDOKLPOH VARCHAR(8)'
    SET @OUP = @OUP + N',@ODDOKLPOH'
  END
  IF @DODOKLPOH IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)<=UPPER(@iDODOKLPOH)'
    SET @INP = @INP + N',@iDODOKLPOH VARCHAR(8)'
    SET @OUP = @OUP + N',@DODOKLPOH'
  END
  IF @ODHLAADR IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)>=UPPER(@iODHLAADR)'
    SET @INP = @INP + N',@iODHLAADR VARCHAR(15)'
    SET @OUP = @OUP + N',@ODHLAADR'
  END
  IF @DOHLAADR IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)<=UPPER(@iDOHLAADR)'
    SET @INP = @INP + N',@iDOHLAADR VARCHAR(15)'
    SET @OUP = @OUP + N',@DOHLAADR'
  END
  IF @ODADRESY IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)>=UPPER(@iODADRESY)'
    SET @INP = @INP + N',@iODADRESY VARCHAR(15)'
    SET @OUP = @OUP + N',@ODADRESY'
  END
  IF @DOADRESY IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)<=UPPER(@iDOADRESY)'
    SET @INP = @INP + N',@iDOADRESY VARCHAR(15)'
    SET @OUP = @OUP + N',@DOADRESY'
  END


  --this select cause a problem ( with SET @SQL=... I get same result )
  SELECT @SQL = N'INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu,
prijato, prikc, priprodkc, vydano, vydkc ) VALUES ( ' +
                N'@iCSKLADU, @iCMATERIALU,' +
                N'SELECT SUM(prijato)' + @SQL2 + N',' +
                N'SELECT SUM(prijato*nakbd)' + @SQL2 + N',' +
                N'SELECT SUM(prijato*prodbd)' + @SQL2 + N',' +
                N'SELECT SUM(vydano)' + @SQL2 + N',' +
                N'SELECT SUM(vydano*prodbd)' + @SQL2 + N')'


  EXEC sp_executesql @SQL,
                     @INP,
                     @OUP


END

i try [i have no mind to solve this problem] use "SET @SQL = CAST(
{upper SQL string } as NVARCHAR(4000) but nothing happend"

Lukas Kolby Barton

No problems here, below is my execution and modification of the procedure:
ALTER PROCEDURE PohybKDatu
  ( @CSKLADU INT, @CMATERIALU VARCHAR(30),
    @ODDATA DATETIME, @DODATA DATETIME,
    @ODHLAADR VARCHAR(15), @DOHLAADR VARCHAR(15),
    @ODDOKLPOH VARCHAR(8), @DODOKLPOH VARCHAR(8),
    @ODADRESY VARCHAR(15), @DOADRESY VARCHAR(15) )
AS
BEGIN
  DECLARE @SQL  NVARCHAR(4000)
  DECLARE @SQL2 NVARCHAR(1000)
  DECLARE @INP  NVARCHAR(1000)
  DECLARE @OUP  NVARCHAR(1000)


  SET @SQL2 = N' FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU'
  IF @ODDATA IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND DATUM>=@iODDATA' +
                        N' AND DATUM<=@iDODATA'
  ELSE
    SET @SQL2 = @SQL2 + N' AND DATUM<@iDODATA'
  SET @INP = N'@iCSKLADU INT, @iCMATERIALU VARCHAR(30), @iODDATA
DATETIME, @iDODATA DATETIME'
  SET @OUP = N'@CSKLADU, @CMATERIALU, @ODDATA, @DODATA'

  IF @ODDOKLPOH IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)>=UPPER(@iODDOKLPOH)'
    SET @INP = @INP + N',@iODDOKLPOH VARCHAR(8)'
    SET @OUP = @OUP + N',@ODDOKLPOH'
  END
  IF @DODOKLPOH IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)<=UPPER(@iDODOKLPOH)'
    SET @INP = @INP + N',@iDODOKLPOH VARCHAR(8)'
    SET @OUP = @OUP + N',@DODOKLPOH'
  END
  IF @ODHLAADR IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)>=UPPER(@iODHLAADR)'
    SET @INP = @INP + N',@iODHLAADR VARCHAR(15)'
    SET @OUP = @OUP + N',@ODHLAADR'
  END
  IF @DOHLAADR IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)<=UPPER(@iDOHLAADR)'
    SET @INP = @INP + N',@iDOHLAADR VARCHAR(15)'
    SET @OUP = @OUP + N',@DOHLAADR'
  END
  IF @ODADRESY IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)>=UPPER(@iODADRESY)'
    SET @INP = @INP + N',@iODADRESY VARCHAR(15)'
    SET @OUP = @OUP + N',@ODADRESY'
  END
  IF @DOADRESY IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)<=UPPER(@iDOADRESY)'
    SET @INP = @INP + N',@iDOADRESY VARCHAR(15)'
    SET @OUP = @OUP + N',@DOADRESY'
  END


  --this select cause a problem ( with SET @SQL=... I get same result )
  SELECT @SQL = N'INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu,
prijato, prikc, priprodkc, vydano, vydkc ) VALUES ( ' +
                N'@iCSKLADU, @iCMATERIALU,' +
                N'SELECT SUM(prijato)' + @SQL2 + N',' +
                N'SELECT SUM(prijato*nakbd)' + @SQL2 + N',' +
                N'SELECT SUM(prijato*prodbd)' + @SQL2 + N',' +
                N'SELECT SUM(vydano)' + @SQL2 + N',' +
                N'SELECT SUM(vydano*prodbd)' + @SQL2 + N')'

SELECT DATALENGTH(@sql)
SELECT @sql
--   EXEC sp_executesql @SQL,
--                      @INP,
--                      @OUP


END
GO
EXEC PohybKDatu 23, 'asdasd', '20051231', '20061223', 'asdasd', 'asdasd', 'asdasd', 'asdasd',
'asdad', 'asdasd'



Show quoteHide quote

Tibor K. Thanks for your opinion - it show me the way which i didnt see
- problem was in INSERT INTO query>

finally output of this function with startup parameters>
dbo.PohybKDatu 88, '3010', NULL, '200601231', NULL, NULL, NULL, NULL,
NULL, NULL, 0, 1

IS:

INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu, prijato, prikc,
priprodkc, vydano, vydkc ) VALUES
(
  @iCSKLADU,
  @iCMATERIALU,
  SELECT SUM(prijato) FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU AND DATUM<@iDODATA,
  SELECT SUM(prijato*nakbd) FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU AND DATUM<@iDODATA,
  SELECT SUM(prijato*prodbd) FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU AND DATUM<@iDODATA,
  SELECT SUM(vydano) FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU AND DATUM<@iDODATA,
  SELECT SUM(vydano*prodbd) FROM skpoh WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU AND DATUM<@iDODATA
)

the problem is in using of SELECT SUM...

it must be :
INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu, prijato, prikc,
priprodkc, vydano, vydkc )
SELECT
  p.cskladu, p.cmaterialu, SUM(p.prijato), SUM(p.prijato*p.nakbd),
SUM(p.prijato*p.prodbd), SUM(p.vydano), SUM(p.vydano*p.prodbd)
FROM skpoh p
WHERE p.cskladu=@iCSKLADU AND p.cmaterialu=@iCMATERIALU AND
p.DATUM<@iDODATA
GROUP BY p.cskladu, p.cmaterialu




so i edit my SP>

CREATE PROCEDURE PohybKDatu
  ( @CSKLADU INT, @CMATERIALU VARCHAR(30),
    @ODDATA DATETIME, @DODATA DATETIME,
    @ODHLAADR VARCHAR(15), @DOHLAADR VARCHAR(15),
    @ODDOKLPOH VARCHAR(8), @DODOKLPOH VARCHAR(8),
    @ODADRESY VARCHAR(15), @DOADRESY VARCHAR(15) )
AS
BEGIN
  DECLARE @SQL  NVARCHAR(4000)
  DECLARE @SQL2 NVARCHAR(1000)
  DECLARE @INP  NVARCHAR(1000)
  DECLARE @OUP  NVARCHAR(1000)





  SET @SQL2 = N' WHERE cskladu=@iCSKLADU AND cmaterialu=@iCMATERIALU'
  IF @ODDATA IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND DATUM>=@iODDATA' +
                        N' AND DATUM<=@iDODATA'
  ELSE
    SET @SQL2 = @SQL2 + N' AND DATUM<@iDODATA'
  SET @INP = N'@iCSKLADU INT, @iCMATERIALU VARCHAR(30), @iODDATA
DATETIME, @iDODATA DATETIME'
  SET @OUP = N'@CSKLADU, @CMATERIALU, @ODDATA, @DODATA'

  IF @ODDOKLPOH IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)>=UPPER(@iODDOKLPOH)'
    SET @INP = @INP + N',@iODDOKLPOH VARCHAR(8)'
    SET @OUP = @OUP + N',@ODDOKLPOH'
  END
  IF @DODOKLPOH IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)<=UPPER(@iDODOKLPOH)'
    SET @INP = @INP + N',@iDODOKLPOH VARCHAR(8)'
    SET @OUP = @OUP + N',@DODOKLPOH'
  END
  IF @ODHLAADR IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)>=UPPER(@iODHLAADR)'
    SET @INP = @INP + N',@iODHLAADR VARCHAR(15)'
    SET @OUP = @OUP + N',@ODHLAADR'
  END
  IF @DOHLAADR IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)<=UPPER(@iDOHLAADR)'
    SET @INP = @INP + N',@iDOHLAADR VARCHAR(15)'
    SET @OUP = @OUP + N',@DOHLAADR'
  END
  IF @ODADRESY IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)>=UPPER(@iODADRESY)'
    SET @INP = @INP + N',@iODADRESY VARCHAR(15)'
    SET @OUP = @OUP + N',@ODADRESY'
  END
  IF @DOADRESY IS NOT NULL
  BEGIN
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)<=UPPER(@iDOADRESY)'
    SET @INP = @INP + N',@iDOADRESY VARCHAR(15)'
    SET @OUP = @OUP + N',@DOADRESY'
  END



  SELECT @SQL = N' INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu,
prijato, prikc, priprodkc, vydano, vydkc )' +
                N' SELECT p.cskladu, p.cmaterialu, SUM(p.prijato),
SUM(p.prijato*p.nakbd), SUM(p.prijato*p.prodbd), SUM(p.vydano),
SUM(p.vydano*p.prodbd)' +
                N' FROM skpoh p' +
                @SQL2 +
                N' GROUP BY p.cskladu, p.cmaterialu'



--  SELECT DATALENGTH(@sql)
--  SELECT @sql





  EXEC sp_executesql @SQL,
                     @INP,
                     @OUP
/* --example
  EXEC sp_executesql N'INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu,
prijato, prikc, priprodkc, vydano, vydkc )
                       SELECT p.cskladu, p.cmaterialu, SUM(p.prijato),
SUM(p.prijato*p.nakbd), SUM(p.prijato*p.prodbd), SUM(p.vydano),
SUM(p.vydano*p.prodbd)
                       FROM skpoh p
                       WHERE cskladu=@iCSKLADU AND
cmaterialu=@iCMATERIALU AND DATUM<@iDODATA
                       GROUP BY p.cskladu, p.cmaterialu',
                     N'@iCSKLADU INT, @iCMATERIALU VARCHAR(30),
@iODDATA DATETIME, @iDODATA DATETIME',
                     N'@CSKLADU, @CMATERIALU, @ODDATA, @DODATA'
*/

END


But now it returns error :
Server: Msg 8114, Level 16, State 4, Line 0
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data
type nvarchar to int.
Failed to get the call stack!

@RETURN_VALUE = N/A

This SP is running from second SP.

Lukas Kolby Barton

I solve the last problem yet, the last parameter musnt be a varchar -
so here is solution:



CREATE PROCEDURE PohybKDatu
  ( @CSKLADU INT, @CMATERIALU VARCHAR(30),
    @ODDATA DATETIME, @DODATA DATETIME,
    @ODHLAADR VARCHAR(15), @DOHLAADR VARCHAR(15),
    @ODDOKLPOH VARCHAR(8), @DODOKLPOH VARCHAR(8),
    @ODADRESY VARCHAR(15), @DOADRESY VARCHAR(15) )
AS
BEGIN
  DECLARE @SQL  NVARCHAR(4000)
  DECLARE @SQL2 NVARCHAR(1000)





  SET @SQL2 = N' WHERE cskladu=@iCSKLADU AND cmaterialu=@iCMATERIALU'
  IF @ODDATA IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND DATUM>=@iODDATA' +
                        N' AND DATUM<=@iDODATA'
  ELSE
    SET @SQL2 = @SQL2 + N' AND DATUM<@iDODATA'

  IF @ODDOKLPOH IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)>=UPPER(@iODDOKLPOH)'
  IF @DODOKLPOH IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND UPPER(doklpoh)<=UPPER(@iDODOKLPOH)'
  IF @ODHLAADR IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)>=UPPER(@iODHLAADR)'
  IF @DOHLAADR IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND UPPER(cishlaadrs)<=UPPER(@iDOHLAADR)'
  IF @ODADRESY IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)>=UPPER(@iODADRESY)'
  IF @DOADRESY IS NOT NULL
    SET @SQL2 = @SQL2 + N' AND UPPER(cisadrs)<=UPPER(@iDOADRESY)'


  --  correct build of SQL query
  SELECT @SQL = N' INSERT INTO tmpPohybKDatu ( cskladu, cmaterialu,
prijato, prikc, priprodkc, vydano, vydkc )' +
                N' SELECT p.cskladu, p.cmaterialu, SUM(p.prijato),
SUM(p.prijato*p.nakbd), SUM(p.prijato*p.prodbd), SUM(p.vydano),
SUM(p.vydano*p.prodbd)' +
                N' FROM skpoh p' +
                @SQL2 +
                N' GROUP BY p.cskladu, p.cmaterialu'


  --  correct using of EXEC sp_executesql
  EXEC sp_executesql @SQL,
                     N' @iCSKLADU INT, @iCMATERIALU VARCHAR(30),
@iODDATA DATETIME, @iDODATA DATETIME, @iODHLAADR VARCHAR(15),
@iDOHLAADR VARCHAR(15), @iODDOKLPOH VARCHAR(8), @iDODOKLPOH VARCHAR(8),
@iODADRESY VARCHAR(15), @iDOADRESY VARCHAR(15)',
                     @CSKLADU, @CMATERIALU, @ODDATA, @DODATA,
@ODHLAADR, @DOHLAADR, @ODDOKLPOH, @DODOKLPOH, @ODADRESY, @DOADRESY


END



Thank you all of them for your help

Lukas Kolby Barton

What are the types of @SQL1, @SQL2, etc?

Try
SET @SQL = 'INSERT INTO.....' + rtrim(@SQL1) + '....' + rtrim(@SQL2)....

Show quoteHide quote

(bar***@modulsoft.cz) writes:
8.00.194 is the RTM version of SQL 2000. You should download and install
SP4 for SQL 2000. There are many bugs that have been fixed. Particularly,
with 8.00.194 you have no protection against the Slammer worm which could
hi-jack your system.

parse out set of numbers from a column
stored procedure stopped working
How is the design for this complex requirement ?
trying to use a cursor to update table..
raise errro in clr procedure