December 11, 2018 at 7:03 am
I have the following statement and I need to use a variable like so:
declare @WhereClause varchar(max)
set @WhereClause = 'customer_code = ' + CHAR(39) + 'XXX12365' + CHAR(39) + ' and skip_slip like ' + CHAR(39) + '%N%' + CHAR(39)
@whereclause will be = to "where customer_code like '%XXX12365%' and ave_row_id = 0 AND skip_slip like '%N%' "
need to use the following statement like so :
select * into ##priceManf from #price @WhereClause
Any help would be appreciated.
December 11, 2018 at 7:54 am
Problem is that your temp table #price is local. It will not be in scope for the dynamic SQL. If you changed it to a global temp table then you can do this:
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'select * into ##priceManf from ##price customer_code = ' + CHAR(39) + 'XXX12365' + CHAR(39) + ' and skip_slip like ' + CHAR(39) + '%N%' + CHAR(39)
EXECUTE(@SQL)
December 11, 2018 at 6:29 pm
shackclan - Tuesday, December 11, 2018 7:54 AMProblem is that your temp table #price is local. It will not be in scope for the dynamic SQL. If you changed it to a global temp table then you can do this:DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'select * into ##priceManf from ##price customer_code = ' + CHAR(39) + 'XXX12365' + CHAR(39) + ' and skip_slip like ' + CHAR(39) + '%N%' + CHAR(39)
EXECUTE(@SQL)
Not correct. Temp tables that are created outside of the Dynamic SQL can be used in the Dynamic SQL without them needing to be global temp tables.
The problem is that the OP only made the WHERE clause dynamic and that's not going to work.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2018 at 6:32 pm
jonathanm 4432 - Tuesday, December 11, 2018 7:03 AMI have the following statement and I need to use a variable like so:declare @WhereClause varchar(max)
set @WhereClause = 'customer_code = ' + CHAR(39) + 'XXX12365' + CHAR(39) + ' and skip_slip like ' + CHAR(39) + '%N%' + CHAR(39)@whereclause will be = to "where customer_code like '%XXX12365%' and ave_row_id = 0 AND skip_slip like '%N%' "
need to use the following statement like so :
select * into ##priceManf from #price @WhereClause
Any help would be appreciated.
I've got to ask... why are you making the WHERE clause dynamic here? It can be used directly as you wrote it although it will have some performance issues because of the mid-string LIKE filter you used (%N%).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2018 at 5:06 am
Jeff,
I stand corrected. Old dogs and new tricks.
create table #tmp
(
number int
)
insert into #tmp (number) values (1)
insert into #tmp (number) values (2)
insert into #tmp (number) values (3)
insert into #tmp (number) values (4)
insert into #tmp (number) values (5)
declare @sql varchar(100)
set @sql = 'select * from #tmp'
execute(@sql)
December 12, 2018 at 6:46 am
shackclan - Wednesday, December 12, 2018 5:06 AMJeff,
I stand corrected. Old dogs and new tricks.
create table #tmp
(
number int
)insert into #tmp (number) values (1)
insert into #tmp (number) values (2)
insert into #tmp (number) values (3)
insert into #tmp (number) values (4)
insert into #tmp (number) values (5)declare @sql varchar(100)
set @sql = 'select * from #tmp'
execute(@sql)
Heh... thanks for coming back and for the test code, which also means you did what a lot of old dogs do... prove that the person saying something is either wrong or they're right with code. Us old dogs gotta stick together. 🙂
Just to share a bit more, I do a lot of ETL and other types of work using temp tables where I don't know what the full definition of the table (Temporary or Permanent) may be until after I load the header and the first couple of lines of a file and parse them with something like DelimitedSplit8K. Then, using that information, use a bit more dynamic SQL to load the data into a "columnized" table, etc, etc. Imagine the possibilities.
Here's a super simple example where a "stub" table is created (borrowing heavily on your code example) where the table structure is first altered and then we do an UPDATE on the table, which is then available "external" to the dynamic SQL. Combine that that with the ability to create temporary stored procedures (yes, they begin with a # sign, as well) and imagine the possibilities of the PFM you can pull off pretty easily.
--===== Create the "stub" temp table and populate it
CREATE TABLE #Tmp
(
Number INT
)
;
INSERT INTO #Tmp
SELECT v.Number
FROM (VALUES (1),(2),(3),(4),(5)) v (Number)
;
--===== Local obviously named variable
DECLARE @SQL VARCHAR(8000)
;
--===== Use dynamic SQL to alter the "stub" table to our needs.
SELECT @SQL = '
ALTER TABLE #Tmp
ADD ReverseNumber AS (6-Number)
,SomeLetter CHAR(1)
;';
EXEC (@SQL)
;
--===== Use dynamic SQL to modify the data in the modified temp table.
SELECT @SQL = '
UPDATE #Tmp
SET SomeLetter = CHAR(Number+64)
;';
EXEC (@SQL)
;
--===== Show that it worked and the table contents are available "external" to the dynamic SQL.
SELECT * FROM #Tmp
;
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply