CODEWITHSUNDEEP
c#sql
jackStonre
jackStonre

Reputation: 3

Why dynamic SQL does not work

Why dynamic SQL does not work ??

code in C# :

 this.DataAccess.AddParametr("@where","WHERE ta.IdMoinRef in(112,113,115)");

code in sqlServer:

ALTER Procedure [dbo].[sp_tblAsnad_SelectAllForReport]
@where nvarchar(max)
As
Begin                                       
Select
        ta.IdMoinRef,
        ta.IdTafzeliRef,
        ta.ShHesab,
        ta.Bd,
        ta.Bs,
        ta.ShSnd,
        ta.AtfSnd,
        ta.DateSnd,
        mo.Hmoin,
        co.Hcol,
        gr.Hgroup,
        co.IdGroupRef,
        mo.IdColRef
    From tblAsnad as ta
    inner join tblMoin as mo on ta.IdMoinRef=mo.IdMoin
    inner join tblCol as co on mo.IdColRef=co.IdCol
    inner join tblGroup as gr on co.IdGroupRef=gr.IdGroup
    exec(@where)
End

Upvotes: 0

Views: 168

Answers (4)

Tim
Tim

Reputation: 5421

You're attempting to execute only the where-clause. For dynamic SQL statements, you must build the entire statement as a string:

           @s = "select * from T " + @where_clause
           exec(@s)

Upvotes: 3

jaywayco
jaywayco

Reputation: 6296

your code is passing the WHERE string in as a string parameter. So what your proc will execute as will be:

ALTER Procedure [dbo].[sp_tblAsnad_SelectAllForReport]
@where nvarchar(max)
As
Begin                                       
Select
        ta.IdMoinRef,
        ta.IdTafzeliRef,
        ta.ShHesab,
        ta.Bd,
        ta.Bs,
        ta.ShSnd,
        ta.AtfSnd,
        ta.DateSnd,
        mo.Hmoin,
        co.Hcol,
        gr.Hgroup,
        co.IdGroupRef,
        mo.IdColRef
    From tblAsnad as ta
    inner join tblMoin as mo on ta.IdMoinRef=mo.IdMoin
    inner join tblCol as co on mo.IdColRef=co.IdCol
    inner join tblGroup as gr on co.IdGroupRef=gr.IdGroup
    exec('WHERE ta.IdMoinRef in(112,113,115)')
End

Which, is obviously wrong. Your entire command needs to be in the command text here.

Upvotes: -1

Joe Stefanelli
Joe Stefanelli

Reputation: 135938

You'd need to EXEC the whole query as one string, not just the WHERE clause.

Upvotes: 2

George Johnston
George Johnston

Reputation: 32278

You're confused. You need to encapsulate your entire statement into a command. You're attempting to execute one query and then an additional query. You need to parse your main query together with your parameter, e.g.

DECLARE @sql VARCHAR(1000)
SET @sql = "SELECT ... " + @where -- Your full query.
exec(@sql)

Upvotes: 4

Related Questions