Update view from dynamic sql

Question

Update view from dynamic sql

SSC-Addicted

Points: 487

June 27, 2023 at 7:31 pm

#4220940

I'm using the code below to change the name of the columns in a view but it doesn't actually update the view. What do I need to do so that the view is updated?

Thanks.

DECLARE @code4 VARCHAR(9); SET @code4 = CONVERT(VARCHAR(2),month(DATEADD(MONTH,DATEDIFF(MONTH, 0, GETDATE()) - 4, 0))) + ' - ' +CONVERT(VARCHAR(4),year(DATEADD(MONTH,DATEDIFF(MONTH, 0, GETDATE()) - 4, 0)))
DECLARE @code3 VARCHAR(9); SET @code3 = CONVERT(VARCHAR(2),month(DATEADD(MONTH,DATEDIFF(MONTH, 0, GETDATE()) - 3, 0))) + ' - ' +CONVERT(VARCHAR(4),year(DATEADD(MONTH,DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)))




DECLARE @query NVARCHAR(MAX);
DECLARE @selects NVARCHAR(MAX);
set @selects = 
'
 code4 AS [' + CAST(@code4 as nvarchar) + ']
,code3 AS [' + CAST(@code3 as nvarchar) + ']
'
set @query = 'SELECT COUNTY_NAME
,VENDOR_ID
,VENDOR_NAME
,ADR_LN_1
,ADR_LN_2
,CITY
,STATE
,SERVICE_CODE
,MOD_CODE_1
,SERVICE,'
+ @selects + 
'FROM BI4.DBO.COE'

EXEC(@query)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

WHY are you using dynamic SQL for this? WHY?

You never tell it to alter the view. How is it supposed to know to alter the view unless you tell it to?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

smattiko83 SSC-Addicted Points: 487 More actions · Answer 2

I am using it to change the column names. I've tried alter view but it does not work.

This reply was modified 1 year, 5 months ago by smattiko83.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

Instead of EXEC(@query)

try PRINT @query

Then try to execute what is printed.

smattiko83 SSC-Addicted Points: 487 More actions · Answer 4

smattiko83

SSC-Addicted

Points: 487

June 28, 2023 at 2:45 am

#4221216

How do I execute what is printed?

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 5

smattiko83 wrote:

How do I execute what is printed?

You can copy it to the clipboard and execute it by pasting into a query window and pressing F5.

But my suggestion was really so that you can look at the query, it will then probably be obvious what the problem is with it.