How Can I Make Code Dynamic

Question

0.00/5 (No votes)

See more:

I have abt 4 tables and i wanna make this query apply for all when i change the table.

By the way may stored procedure query is working perfectly but only if i make the table static.

Below is my code. I've commented the dynamic code out because it wasn't working

Will be glad if the experts edit it :)

SQL

ALTER PROC [dbo].[spTest]
(
	@Selectstatement VARCHAR (255) --Show ALL--
	--@Table_name  sysname
)
AS
BEGIN
		--DECLARE @Dynamictbl nvarchar(255)
	-- IF Conditions--

	IF @Selectstatement = 'ALL'
		BEGIN
		--SET @Dynamictbl = ('SELECT * FROM ' + @Table_name + ' WHERE [Geschäftsbereich]=''AB''')
			(SELECT * 
				FROM tbl201102  
				WHERE [Geschäftsbereich]='AB')
				ORDER BY [Projectname] ASC
		RETURN
		END

So this is how i went abt it but wenn i execute notthing happens

SQL

ALTER PROC [dbo].[spTest]
(
	@Selectstatement VARCHAR (255) --Show ALL--
	@Table_name  sysname
)
AS
BEGIN
		DECLARE @Dynamictbl nvarchar(255)
	-- IF Conditions--

	IF @Selectstatement = 'ALL'
		BEGIN
	SET @Dynamictbl = ('SELECT * FROM ' + @Table_name + ' WHERE [Geschäftsbereich]=''AB''')
			
		RETURN
		END
END
GO

EXEC spTest @Selectstatement = 'ALL', @Table_name = 'tbl201409'

Posted 15-Jan-15 6:50am

mikybrain1

Updated 15-Jan-15 8:00am

v4

Add a Solution

Comments

mikybrain1 15-Jan-15 13:50pm

Hi Experts any help?

2 solutions

Solution 2

You can combine your query script by program, and pass it as then parameter of the stored procedure, and execute it by keyword "EXEC"(Sample: exec('select * from mytable')) in the stored procedure. But you have to understand, when you called a stored procedure, a new session was created, and when you execute a script by keyword “EXEC” in stored procedure, another session was created for executing. So, if you want write any complex script in the stored procedure(for example, in stored procedure, you have to query a record set by a dynamic script, and do something others to the record set), you should use global temp table(table name start with ##) of SQL Server but not normal temp table(table name start with #), it can share to different session(you can access normal a temp table in same session only). So. In this case, I always write like following script:

SQL

Declare @cStatemtn nvarchar(max)
Declare @cFieldValue1 nvarchar(100)

Set @cStatement = 'select * from mytable into ##tmpTbl where field1 = ' + @cFieldValue1

if not (select object_id('tempdb.. ##tmpTbl ')) is null drop table ##tmpTbl
Exec(@cStatement)

Select [ID], [Name], sum(score) from ##tmpTbl
Group by ID, [ID], [Name]
if not (select object_id('tempdb.. ##tmpTbl ')) is null drop table ##tmpTbl

Posted 15-Jan-15 15:14pm

KentBill

Updated 15-Jan-15 19:31pm

v2

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)

**Zoltán Zörgő** · Accepted Answer · 2015-01-15T07:55:00

Of course. You compose the dynamic query string (let's suppose the result is syntactically correct), but you don't execute it. Check here for example: Building Dynamic SQL In a Stored Procedure[^], http://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/[^]

SQL

ALTER PROC [dbo].[spTest]
(
    @Selectstatement VARCHAR (255) --Show ALL--
    @Table_name  sysname
)
AS
BEGIN
        DECLARE @Dynamictbl nvarchar(255)
    -- IF Conditions--

    IF @Selectstatement = 'ALL'
        BEGIN
    SET @Dynamictbl = ('SELECT * FROM ' + @Table_name + ' WHERE [Geschäftsbereich]=''AB''')
        EXECUTE @Dynamictbl
        RETURN
        END
END
GO