CODEWITHSUNDEEP
sqlsql-servertemp-tables
sisty
sisty

Reputation: 23

Why can't create the #Articole table

I try to create one tenp table (#Articole) but without succes. Where is the mistake?

Declare @Articol int,
       @Sql NVarchar(max)
set @Articol = 99190
set @Sql=N'
select im.ItemName, im.ItemCode, d.ItemId as PromoId, im.Enabled, im.ItemId, dfv.DiscountFilterValueId
into #Articol
from Discount d (nolock) 
join DiscountFilter df (nolock) on df.DiscountId = d.ItemId
Join DiscountFilterValue dfv (nolock) on dfv.DiscountFilterId = df.DiscountFilterId
join Item i (nolock) on d.ItemId = i.ItemId
Join Item im (nolock) on im.ItemId = dfv.TextValue
where df.EntityId = 184'

IF @Articol <> -1 set @Sql = @Sql + ' and im.ItemId = @Articol'
If @Articol = -1 set @Sql = @Sql
exec sp_executesql @Sql, N'@Articol int', @Articol

select * from #Articole

Upvotes: 0

Views: 49

Answers (3)

Ismail
Ismail

Reputation: 1316

We can create temporary table in two scopes,

  1. Local Scope - created by declaring with '#' e.g #Articole
  2. Global Scope - created by declaring with '##' e.g ##Articole

Either create you temp Table outside the local scope or create it as globally.

Upvotes: 0

Iman Kazemi
Iman Kazemi

Reputation: 552

Try this:

    CREATE TABLE #Articole (ItemName NVARCHAR(MAX), ItemCode INT, ItemId INT, Enabled BIT, SecondItemId INT, DiscountFilterValueId INT)
    SET @Sql=N'
    insert into #Articol
    select im.ItemName, im.ItemCode, d.ItemId as PromoId, im.Enabled, im.ItemId, dfv.DiscountFilterValueId      
    from Discount d (nolock) 
    join DiscountFilter df (nolock) on df.DiscountId = d.ItemId
    Join DiscountFilterValue dfv (nolock) on dfv.DiscountFilterId = df.DiscountFilterId
    join Item i (nolock) on d.ItemId = i.ItemId
    Join Item im (nolock) on im.ItemId = dfv.TextValue
    where df.EntityId = 184'

    IF @Articol <> -1 set @Sql = @Sql + ' and im.ItemId = @Articol'
    If @Articol = -1 set @Sql = @Sql
    exec sp_executesql @Sql, N'@Articol int', @Articol

    select * from #Articole

I didn't know your columns type, I just guess it. Make sure types are proper

Upvotes: 1

Tanveer Badar
Tanveer Badar

Reputation: 5524

EDIT You do not need dynamic sql here at all. This will suffice in your original query: and (@articol = -1 or im.ItemId = @articol).

A temporary table is limited to the scope which created it. You either need to create it before sp_executesql, or move rest of your statements inside the dynamic query string itself, or make it a global temporary table by prefixing with ## instead of a single #.

Since I do not know the schema of the tables in actual query, below is a trick to create it beforehand.

select top 0 im.ItemName, im.ItemCode, d.ItemId as PromoId, im.Enabled, im.ItemId, dfv.DiscountFilterValueId
into #Articol
from Discount d (nolock) 
join DiscountFilter df (nolock) on df.DiscountId = d.ItemId
Join DiscountFilterValue dfv (nolock) on dfv.DiscountFilterId = df.DiscountFilterId
join Item i (nolock) on d.ItemId = i.ItemId
Join Item im (nolock) on im.ItemId = dfv.TextValue
where df.EntityId = 184

@Sql NVarchar(max)
set @Articol = 99190
set @Sql=N'
insert into #Articol
select im.ItemName, im.ItemCode, d.ItemId as PromoId, im.Enabled, im.ItemId, dfv.DiscountFilterValueId
from Discount d (nolock) 
join DiscountFilter df (nolock) on df.DiscountId = d.ItemId
Join DiscountFilterValue dfv (nolock) on dfv.DiscountFilterId = df.DiscountFilterId
join Item i (nolock) on d.ItemId = i.ItemId
Join Item im (nolock) on im.ItemId = dfv.TextValue
where df.EntityId = 184'

IF @Articol <> -1 set @Sql = @Sql + ' and im.ItemId = @Articol'
If @Articol = -1 set @Sql = @Sql
exec sp_executesql @Sql, N'@Articol int', @Articol

select * from #Articole

Upvotes: 0

Related Questions