Using SQL Functions within EXEC Statement 2

[kjv1611]

From all I can find so far, apparently, it's not possible.

Is there a way, short of programming something via Visual Studio, to build a statement that works like this?

EXEC('SELECT LEF(MyString,12) AS MyString ,REPLACE(MyField,-,) FROM MyTable')

This is a very simplistic example, and may make no sense, I'm sure. I'll try to post some further notes on what I'm trying to do either this evening or tomorrow morning.

Thanks for any suggestions if anyone has a clue as to what I'm trying to accomplish here.

 

[markros]

You can use EXEC ('Select something from myTable')

or

declare @SQL nvarchar(max)

set @SQL = -- here goes dynamic SQL

execute (@SQL)

PluralSight Learning Library

 

[SimonSellick]

I haven't come across any serious restrictions in what you can run through an EXEC call, but specifying the strings can be a bit challenging when single quotes are needed, especially if you want to include variables from the caller. For example:

...
SET @sql = '
  UPDATE Tbl
    SET Col = ''Val-' + @Variable + '''
  WHERE Col IS NULL
'

Getting results back to the caller can also be difficult as the expected fields (e.g. @@rowcount) refer to the execution of the script (@sql), not what it did.

 

[kjv1611]

Thanks for both of your suggestions. I'll try the method of putting it in a variable - tried yesterday, but I forgot the nvarchar part.

And yes, it does seem that the primary cause for concern is with those functions that include single quotes. I'll see if I can play with it today and get that sorted out.

I'll do my best to post back within the next few hours.

 

[kjv1611]

For sure, those were the 2 pieces I was missing... and possibly using EXECUTE instead of EXEC? Not sure which would be better.

Anyway, I got it sorted out on how to do the single quotes - just put 2 anywhere you'd of put one.

What I was trying to do was either do without them or just concatenate multiple strings. I just didn't think about needing the actual quotation marks in there.

So, my final code that I was having issue looks something like this:

DECLARE @DateRun varchar (10)
DECLARE @User varchar (80)
DECLARE @Insert nvarchar (max)

SET @DateRun = CONVERT(VARCHAR(10),GETDATE(),112) --caputures today's date in YYYYMMDD format
SET @User = '[' + USER + ']'
SET @Insert = 'INSERT INTO [Sandbox].' + @User + '.[Test' + @DateRun + '](
		 [RecordID]	,[PC] 
		,[LName] ,[FName] ,[MName] ,[Suffix] 
		,[Add1] ,[Add2] ,[City] ,[State] ,[Zip] 
		,[Social] ,[Date] 
		,[Gen1] ,[Gen2]
		)
	SELECT	TOP (100)
			 b.[AcctID] AS [RecordID] ,''ABC1'' AS [PC]
			,LEFT(b.[LastName], 25) AS [LName] 
			,LEFT(b.[FirstName], 25) AS [FName] ,b.[MiddleName] AS [MName]
			,b.[Suffix] ,b.[Address] AS [Add1] 
			,LEFT(b.[Address2],25) AS [Add2]
			,b.[City] ,b.[State] ,LEFT(REPLACE(b.[Zip],''-'',''''),9) AS [Zip]
			,LEFT(REPLACE(b.[SSN],''-'',''''),9) AS [Social] ,a.[MyDate]
			,b.[Seq] AS [Gen1]
			,CONVERT(datetime,GetDate(),120) AS [Gen2] 
	FROM	[TableA] a WITH (NOLOCK)
				INNER JOIN
			[TableB] b WITH (NOLOCK)
				ON a.[ID] = b.[ID]
	WHERE	a.[Status] LIKE ''Some%'' 
	ORDER BY NewID()'

EXECUTE(@Insert)

And this brings up another related question: Is there a way to erase, clear, or set to a null string, the varchar/nvarchar variables? That's something I like to do in VBA as a bit of a clean-up. I realize it may be overkill, but I'd like to know how (if possible).

Thanks again for the help!

 

[markros]

The variables are only visible in the batch that created it. In SQL Server you don't need to erase the variables or set them to null - this is done by SQL Server itself and you don't have much control.

For the temp tables you create I recently learned it's also good to let SQL Server to drop them rather than dropping them explicitly (unless you need to re-use the same name, of course).

PluralSight Learning Library