Dynamic SQL Server stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

Hi Can someone help? This query isn't working when i make it a stored procedure!!!
Maybe i have less inverted commas / quotation marks

SQL

ALTER PROC spVM
(

@Table_Name sysname,
@Table_Name2 sysname

)
AS
BEGIN
SET NOCOUNT OFF;

 DECLARE @Dynamictbl nvarchar(255)

 SET @Dynamictbl = 
N'SELECT DISTINCT
	t1.[Projektdefinition DB] AS [Projektdefinition DB 201409BS],
	t1.[Projektdefinition DB1] AS [Projektbezeichung DB 201409BS],

	t1.[GWU Vorschau mit AZ] AS [GWU_Per201409BS_IST],
	t2.[GWU Vorschau mit AZ] AS [GWU_Per201408BS IST],
	t1.[GWU Vorschau mit AZ] - t2.[GWU Vorschau mit AZ] AS [GWU IST DELTA],

	t1.[GWU Planung mit AZ] AS [GWU Per201409BS SOLL],
	t2.[GWU Planung mit AZ] AS [GWU_SOLL],
	t1.[GWU Planung mit AZ] - t2.[GWU Planung mit AZ] AS [GWU SOLL DELTA],

	t1.[IST Vorjahre mit AZ] AS [Ist Vorjahre],
	t2.[IST Vorjahre mit AZ] AS [Ist Vorjahre],

	t1.[Ist Vorjahr mit AZ] AS [Ist Vorjahr],
	t2.[Ist Vorjahr mit AZ] AS [Ist Vorjahr],

	t1.[IST lfd#GJ mit AZ] AS [Ist lfd GJ],
	t2.[IST lfd#GJ mit AZ] AS [Ist lfd GJ],

	t1.[Obligo BANF lfd# GJ] AS [Obligo BANF Ifd GJ],
	t2.[Obligo BANF lfd# GJ] AS [Obligo BANF Ifd GJ],

	t1.[Vorschau lfd# GJ mit AZ] AS [Vorschau Ifd GJ],
	t2.[Vorschau lfd# GJ mit AZ] AS [Vorschau Ifd GJ],

	t1.[Vorschau Folgejahr mit AZ] AS [Vorschau Folgejahr],
	t2.[Vorschau Folgejahr mit AZ] AS [Vorschau Folgejahr],

	t1.[Vorschau GJ+02 mit AZ] AS [Vorschau GJ+02],
	t2.[Vorschau GJ+02 mit AZ] AS [Vorschau GJ+02],

	t1.[Vorschau GJ+03 mit AZ] AS [Vorschau GJ+03],
	t2.[Vorschau GJ+03 mit AZ] AS [Vorschau GJ+03],

	t1.[Vorschau GJ+04 mit AZ] AS [Vorschau GJ+04],
	t2.[Vorschau GJ+04 mit AZ] AS [Vorschau GJ+04],

	t1.[Vorschau GJ+05 mit AZ] AS [Vorschau GJ+05],
	t2.[Vorschau GJ+05 mit AZ] AS [Vorschau GJ+05],

	t1.[Vorschau GJ+06 mit AZ] AS [Vorschau GJ+06],
	t2.[Vorschau GJ+06 mit AZ] AS [Vorschau GJ+06],

	t1.[Vorschau GJ+07 mit AZ] AS [Vorschau GJ+07],
	t2.[Vorschau GJ+07 mit AZ] AS [Vorschau GJ+07],


	t1.[Planung GJ+06 mit AZ] AS [Planung GJ+06],
	t2.[Planung GJ+06 mit AZ] AS [Planung GJ+06],

	
	t1.[IST lfd# GJ zum Obligostichtag mit AZ] AS [Obligostichtag],
	t2.[IST lfd# GJ zum Obligostichtag mit AZ] AS [Obligostichtag]

FROM ' + @Table_Name + ' AS t1
LEFT OUTER JOIN ' + @Table_Name2 + ' AS t2 
ON  t1.[Projektdefinition DB] =  t2.[Projektdefinition DB]
WHERE t1.[Geschäftsbereich] = ''PB'' AND t2.[Geschäftsbereich] = ''PB''' + 
' ORDER BY t1.[Projektdefinition DB] ASC '

EXECUTE sp_executesql @Dynamictbl
END
GO
EXEC spVM 'tbl2', 'tbl1'

Posted 28-Jan-15 0:52am

mikybrain1

Updated 28-Jan-15 2:34am

v4

Add a Solution

Comments

Umer Akram 28-Jan-15 7:17am

quick question what error it gives you when you execute this query? if you want to use space in side column name i would suggest to use [] instead of "". e.g
t1.[Planung GJ+05 mit AZ] AS "Planung GJ+05"
will become
t1.[Planung GJ+05 mit AZ] AS [Planung GJ+05]

mikybrain1 28-Jan-15 7:29am

Ok. I tried that one too but the same error
ERROR 105 (GW_Per201408)
and 102 (False syntax in near from GW_Per201408)
Thnx

3 solutions

Solution 1

Hi mikybrain,

I would suggest you to run your dynamic query and use SQL Profiler to grab the parsed T-SQL script from SQL Profiler and convert that SQL to stored procedures.

If you dont have rights to execute SQL profiler, you can also try Express Profiler available in the link to grab your final T-SQL Query.

Standalone sql profiler - ExpressPreofiler[^]

https://expressprofiler.codeplex.com[^]

Posted 28-Jan-15 1:01am

Liju Sankar

Solution 2

your main issue is the length of your variable @Dynamictbl nvarchar(255)

its should be

SQL

DECLARE @Dynamictbl nvarchar(MAX)

update it and execute your sp and see the result.

Posted 28-Jan-15 1:42am

Umer Akram

Updated 28-Jan-15 2:56am

v2

Comments

CHill60 28-Jan-15 8:00am

My apologies - we were posting the same answer at the same time ... or rather I hadn't refreshed my screen!

CHill60 28-Jan-15 8:58am

And again!! :-D

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-01-28T01:50:00

Solution 3

See the section

VB

t2.[Planung GJ+05 mit AZ] AS "Planung GJ+05",


FROM ' + @Table_Name + ' AS t1

You have a comma after the last column - this will throw an error. Try changing it to

VB

t2.[Planung GJ+05 mit AZ] AS "Planung GJ+05"


FROM ' + @Table_Name + ' AS t1

I also agree with the comment from Umer Akram - use square brackets [] instead of double quotes to make it clearer

Also your query is longer than 255 characters - change the declare to

SQL

DECLARE @Dynamictbl nvarchar(MAX)

Posted 28-Jan-15 1:50am

CHill60

Updated 28-Jan-15 2:57am

v2

Comments

mikybrain1 28-Jan-15 7:59am

Thnx experts But it still not working. It was my mistake with the ',' i shorten the code because it was too long

CHill60 28-Jan-15 8:12am

Please see the comment from Umer Akram below this post

Umer Akram 28-Jan-15 8:26am

this means your select clause is also dynamic means?

quick question, are the used table are also dynamic means generated on the run time ?

you have to break your code to narrow down the issue, i would suggest to validate the FROM WHERE clause 1st something like this. this will ensure that your query base is correct.

N'Select *
FROM ' + @Table_Name + ' AS t1
LEFT OUTER JOIN ' + @Table_Name2 + ' AS t2
ON t1."Projektdefinition DB" = t2."Projektdefinition DB"
WHERE t1.[DBN - bereich] = ''PB'' AND t2.[DBN - bereich] = ''PB''' +
' ORDER BY t1.[Projektdefinition DB] ASC '

one more thing what is the data type of the variables @Table_Name , @Table_Name2

and how you are execution this query ?

CHill60 28-Jan-15 8:28am

What is the content of @Table_Name and @Table_Name2?

mikybrain1 28-Jan-15 8:35am

I've updated my question with the exact sp

CHill60 28-Jan-15 8:57am

I've updated my solution

Umer Akram 28-Jan-15 8:08am

replied to OP.

CHill60 28-Jan-15 8:11am

Good advice! However I got notified instead of the OP :)

mikybrain1 28-Jan-15 8:27am

My query is correct and it giving me the right records when execute with static tables.
Example: SELECT DISTINCT *
FROM tbl1 AS t1
LEFT OUTER JOIN tbl2 AS t2
ON t1.[Projektdefinition DB] = t2.[Projektdefinition DB]
WHERE t1.[DBN - Geschäftsbereich] = 'PB' AND t2.[DBN - Geschäftsbereich] = 'PB'
ORDER BY t1.[Projektdefinition DB]

And that's how i exec my sp:

EXEC spExample 'tbl2', 'tbl1'

Umer Akram 28-Jan-15 8:30am

This means that you need to validate your select statement. you might be using a column(s) which are not related to the tables.

how you are creating your select query? can you share the logic?

mikybrain1 28-Jan-15 8:36am

I've updated my question

Umer Akram 28-Jan-15 8:26am

my mistake, better reply to him then :)