sp_executesql problems

4 Aug 2006 2:04 PM

paulwragg2323

Hi

Apologies for the case of my posting below but I upper cased everything
and I don't have time to change it all. I am having problems using
sp_executesql in that the output parameter does not seem to get the
value from the SQL statement that is executed. I have changed the table
name and column name for privacy purposes but this is pretty much what
I am doing in reality. I am actually doing this within a cursor loop
but as soon as I find something that exceeds a limit I wish to raise an
error then exit. I think everything is correct but the sp_executesql. I
have read the help so please don't point me to that as I really do
think I am doing everything correctly. Can anybody help please?

DECLARE
@STRSQL NVARCHAR(4000),
@NMAXLENGTH    INT,
@STRCOLUMNNAME    VARCHAR(4000),
@NVALUE1    INT

BEGIN

SET @NMAXLENGTH = 0
SET @STRCOLUMNNAME = 'COLUMN1'
SET @NVALUE1 = 3
SET @STRSQL = 'SELECT MAX(DATALENGTH(' + @STRCOLUMNNAME + '))
                    FROM TABLE1
                    WHERE COLUMN1 = ' + CAST(@NVALUE1 AS VARCHAR(9))

        EXEC SP_EXECUTESQL @STRSQL,
        N'@NMAXLENGTH INT OUTPUT',
        @NMAXLENGTH OUTPUT

IF @NMAXLENGTH > 100
BEGIN
RAISERROR('LENGTH EXCEEDED',16,1)
END
END

Many Thanks in advance,

Paul

4 Aug 2006 2:16 PM

Steve Kass

Paul,

@NMAXLENGTH won't get a value because it is not mentioned
anywhere in your query. Unfortunately, sp_executesql is not
clairvoyant. You need to assign the maximum data length to the
specific output parameter in the SQL string:

SET @STRSQL = 'SELECT @NMAXLENGTH = MAX(DATALENGTH(' + @STRCOLUMNNAME + '))
                    FROM TABLE1
                    WHERE COLUMN1 = ' + CAST(@NVALUE1 AS VARCHAR(9))

        EXEC SP_EXECUTESQL @STRSQL,
        N'@NMAXLENGTH INT OUTPUT',
        @NMAXLENGTH OUTPUT

Steve Kass
Drew University
www.stevekass.com

paulwragg2***@hotmail.com wrote:

Show quote

>Hi
>
>Apologies for the case of my posting below but I upper cased everything
>and I don't have time to change it all. I am having problems using
>sp_executesql in that the output parameter does not seem to get the
>value from the SQL statement that is executed. I have changed the table
>name and column name for privacy purposes but this is pretty much what
>I am doing in reality. I am actually doing this within a cursor loop
>but as soon as I find something that exceeds a limit I wish to raise an
>error then exit. I think everything is correct but the sp_executesql. I
>have read the help so please don't point me to that as I really do
>think I am doing everything correctly. Can anybody help please?
>
>DECLARE
>@STRSQL NVARCHAR(4000),
>@NMAXLENGTH    INT,
>@STRCOLUMNNAME    VARCHAR(4000),
>@NVALUE1    INT
>
>BEGIN
>
>SET @NMAXLENGTH = 0
>SET @STRCOLUMNNAME = 'COLUMN1'
>SET @NVALUE1 = 3
>SET @STRSQL = 'SELECT MAX(DATALENGTH(' + @STRCOLUMNNAME + '))
>                    FROM TABLE1
>                    WHERE COLUMN1 = ' + CAST(@NVALUE1 AS VARCHAR(9))
>
>        EXEC SP_EXECUTESQL @STRSQL,
>        N'@NMAXLENGTH INT OUTPUT',
>        @NMAXLENGTH OUTPUT
>
>
>IF @NMAXLENGTH > 100
>BEGIN
>RAISERROR('LENGTH EXCEEDED',16,1)
>END
>END
>
>
>Many Thanks in advance,
>
>Paul
>
>
>

4 Aug 2006 2:26 PM

paulwragg2323

Fantastic, I had just assumed that by saying the output parameter would
be nMaxLength that the result would go in to it, but now its obvious
where I was wrong.

Thanks again,

Paul

4 Aug 2006 2:47 PM

Arnie Rowland

While you may have 'read the help', I suggest that the following resource
will prove to be quite useful to you.

http://www.sommarskog.se/dynamic_sql.html

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

<paulwragg2***@hotmail.com> wrote in message

Show quote

news:1154700264.830622.259420@i42g2000cwa.googlegroups.com...
> Hi
>
> Apologies for the case of my posting below but I upper cased everything
> and I don't have time to change it all. I am having problems using
> sp_executesql in that the output parameter does not seem to get the
> value from the SQL statement that is executed. I have changed the table
> name and column name for privacy purposes but this is pretty much what
> I am doing in reality. I am actually doing this within a cursor loop
> but as soon as I find something that exceeds a limit I wish to raise an
> error then exit. I think everything is correct but the sp_executesql. I
> have read the help so please don't point me to that as I really do
> think I am doing everything correctly. Can anybody help please?
>
> DECLARE
> @STRSQL NVARCHAR(4000),
> @NMAXLENGTH INT,
> @STRCOLUMNNAME VARCHAR(4000),
> @NVALUE1 INT
>
> BEGIN
>
> SET @NMAXLENGTH = 0
> SET @STRCOLUMNNAME = 'COLUMN1'
> SET @NVALUE1 = 3
> SET @STRSQL = 'SELECT MAX(DATALENGTH(' + @STRCOLUMNNAME + '))
> FROM TABLE1
> WHERE COLUMN1 = ' + CAST(@NVALUE1 AS VARCHAR(9))
>
> EXEC SP_EXECUTESQL @STRSQL,
> N'@NMAXLENGTH INT OUTPUT',
> @NMAXLENGTH OUTPUT
>
>
> IF @NMAXLENGTH > 100
> BEGIN
> RAISERROR('LENGTH EXCEEDED',16,1)
> END
> END
>
>
> Many Thanks in advance,
>
> Paul
>