Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2003
    Posts
    16

    Angry Unanswered: Probleme whit (EXECUTE sp_executesql )

    Hollo everybody

    Why have problème whit this code

    This is errer msg
    Server: Msg 170, Level 15, State 1, Line 1
    Line 1: Incorrect syntax near 'non'.

    DECLARE @pConvID as integer
    DECLARE @pclauseID as integer
    Set @pConvID = 229
    Set @pclauseID = 1
    DECLARE @cpt as integer
    DECLARE @SQL as nvarchar(1000)
    DECLARE @pClauseTableName as varchar(50)

    SELECT @pClauseTableName = Ltrim(Rtrim(cTableName)) FROM tblRefLstClauses WHERE iClauseID=@pClauseID

    SELECT @SQL = N'SET @cpt=(Select Count(*) FROM ' + @pClauseTableName + ' WHERE ' + @pClauseTableName + '_est_analyse = ''non''' --<--Probleme whit this value???

    EXECUTE sp_executesql @SQL, N'@cpt integer OUTPUT', @cpt OUTPUT

    SELECT @cpt

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try this:

    Code:
    SELECT @SQL = 'SET @cpt=(Select Count(*) FROM ' 
    	+ @pClauseTableName 
    	+ ' WHERE ' 
    	+ @pClauseTableName 
    	+ '_est_analyse = '
    	+ '''' + 'non' + ''''

    Oh...just checking this...you won't be able to assign a variable like that...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Posts
    16
    have same message! ???

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try this

    Code:
    CREATE TABLE #myTable99 (Col1 int)
    
    SELECT @SQL = 'INSERT INTO  #myTable99 (Col1) Select Count(*) FROM ' 
    	+ @pClauseTableName 
    	+ ' WHERE ' 
    	+ @pClauseTableName 
    	+ '_est_analyse = '
    	+ '''' + 'non' + '''' 
    
    EXEC (@SQL)
    
    SELECT @Cpt = SELECT Col1 FROM #myTable99
    
    DROP TABLE #myTable99
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Oct 2003
    Posts
    16
    Yes, that ok
    Why whit sp_executesql is not good ??

    DECLARE @pConvID as integer
    DECLARE @pclauseID as integer
    Set @pConvID = 229
    Set @pclauseID = 1
    DECLARE @cpt as integer
    DECLARE @SQL as nvarchar(1000)
    DECLARE @pClauseTableName as varchar(50)
    SELECT @pClauseTableName = Ltrim(Rtrim(cTableName)) FROM tblRefLstClauses WHERE iClauseID=@pClauseID

    CREATE TABLE #myTable99 (Col1 int)

    SELECT @SQL = 'INSERT INTO #myTable99 (Col1) Select Count(*) FROM '
    + @pClauseTableName
    + ' WHERE '
    + @pClauseTableName
    + '_est_analyse = '
    + '''' + 'non' + ''''

    EXEC (@SQL)

    SELECT @cpt = Col1 FROM #myTable99

    Select @cpt

    DROP TABLE #myTable99


    [/CODE] [/SIZE][/QUOTE]

  6. #6
    Join Date
    Oct 2003
    Posts
    16
    Ok Brett it Ok for me.

    Thank you very much!

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ericjean
    Yes, that ok
    Why with sp_executesql is not good ??


    Because you can't assign a variable in that manner

    Think of sp_executesql as an independant thread that does not "live" inside the current transaction. It's wrapped around by it, and will wait for it to complete, but it's "outside" of your current spid (I think)

    Anyone with a better explination (esp if I'm waaaaaay off base)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Posts
    5

    Red face Probleme whit (EXECUTE sp_executesql ) or Col Name as parameter in stored Procedure

    Dear Brett Kaiser

    I have code like this

    Declare @AcctCode Char(8)
    Declare @Stmt nVarChar(100)
    Set @AcctCode ='An_sales'
    Set @Stmt= 'Select AVG(' + @AcctCode + ') From T_Comp_Profile'
    Exec SP_ExecuteSql @stmt

    I want to store the value returned by "Exec SP_ExecuteSql @stmt"
    in some variable so i tried
    Declare @rtVal Numeric (18,5)
    Exec SP_ExecuteSql @rtval =@stmt
    But the above stt returns a data set

    I can do this by using " Temporary tables" but i dont want to use them
    I also tried using "Tables Data type" but even that cant help me.

    Pls suggest any other beeter way to do this

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How about:

    Select AVG(AcctCode) From T_Comp_Profile WHERE AcctCode = @AcctCode


    AVOID DYNAMIC SQL..

    You don't need it..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2003
    Posts
    5

    Problem with SP_ExecuteSql

    Originally posted by Brett Kaiser
    How about:

    Select AVG(AcctCode) From T_Comp_Profile WHERE AcctCode = @AcctCode


    AVOID DYNAMIC SQL..

    You don't need it..

    I need to pass column name i.e Acctcode as variable to a stored procedure and want to get the return value in a variable so that i can use that value

    the stored procedure is some thing like


    CREATE PROCEDURE P_Get_Avg_Sales

    @AcctCode Char(8),
    @rtVal Numeric (18,5) OUTPUT

    AS

    Declare @Stmt nVarChar(100)
    Set @Stmt = 'Select AVG(' + @AcctCode + ') From T_Comp_Profile'

    Exec SP_ExecuteSql @rtVal=@Stmt
    print @rtval

    GO

    The stt "Exec SP_ExecuteSql @rtVal=@Stmt " displays the value but doesnt copy that in @rtVal variable so that i can use that

    Actually the stt "Exec SP_ExecuteSql @rtVal=@Stmt " returns a data grid
    I dont want to use Temporry tables

    I have also tried using table data type

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How many columns in a table?

    Dont use dynamic sql..

    Long live the queen

    Where's my lost shaker of salt?

    IF @Param = 'x'
    BEGIN
    SELECT @z=AVG(Col1)...
    END
    If @Param = 'y'
    BEGIN
    SELECT @x = AVG(Col2)...
    END


    Otherwise you need dynamic to INSERT INTO a table


    Don't use dynamic sql...it leads....

    to the dark side...


    ooh the disconnected rambling thing again...where's my medication (read margarita)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brett dude, you are getting weird on us.

    I have to pipe in here. Even though I avoid using dynamic sql whenever possible, I think it is often faster than using a lot of conditional logic in your procedure or SQL statement. The two big beefs with dynamic sql is that it is hard to debug, but mostly that it cannot be precompiled. But when a SQL statement is loaded up with case statements and other conditional logic, often the existing query plan is not appropriate for many of the parameters submitted. I have seen certain cases where dynamic sql runs much faster than direct sql.

    The dark side? I don't think so, but it's definitely on the gray side...

    Have another margarita.

    blindman

  13. #13
    Join Date
    Sep 2003
    Posts
    522
    actually, there is a bigger issue with d-sql, - your security goes out the door. with d-sql you have to explicitly give all needed permissions to everybody who will execute your stored procedure. that's a double whammy: stored procedure + the table that your sp does d-sql against.

    i agree with Brett, avoid it. instead you can do your average against all columns, put it into a temp table, and then make an assignment to your output parameter based on the value of the passed argument.

    i just don't understand why there is so much negativity against temporary tables while trying to execute a dynamic sql?

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Brett dude, you are getting weird on us.
    You have no idea...disconnected ramblings are your friend...

    try this..esp if the sql is in a url...

    Code:
    DECLARE @SQL varchar(8000)
    SELECT @SQL = 'SELECT * FROM sysobjects GO SELECT ' + '''' + 'DROP DATABASE x_Northwind...kinda scary aint it?' + ''''
    EXEC (@SQL)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    True. I would not recommend concatenating input parameters into dynamic SQL statements for the security reasons you specify. I'm talking about constructing the statements based upon evaluation of the input parameters.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •