April 28, 2021 at 6:45 am
Ahoi,
is there a way to do something like this pseudo code?
DECLARE @counting int = 1
WHILE (@counting < 3)
BEGIN
DECLARE @DYNAMIC_Variable+@counting NVARCHAR(200)
SET @counting = @counting +1
END
--Wish result:
DECLARE @DYNAMIC_Variable1 NVARCHAR(200)
DECLARE @DYNAMIC_Variable2 NVARCHAR(200)
My problem is i need a dynamic number of variables and i need to access all the variables at the SAME time in a STRING.
I have seen that its possible to create a table variable, but i don't know (if even possible) to acess a dynamic number of these values at the same time as replacement in a string like here: + @DYNAMIC_Variable1 +
DECLARE @DYNAMIC_Variable1 NVARCHAR(200) = 'x'
DECLARE @DYNAMIC_Variable2 NVARCHAR(200) = 'Y'
select
'
select [' + @DYNAMIC_Variable1 + '],[' + @DYNAMIC_Variable2 + ']
'
--Here is the pseudo code of what im talking about using multiple table variable rows in a string
declare @vars table (
id int identity(1, 1),
loopcntr int,
value varchar(255)
);
insert into @vars(loopcntr, value)
select 1, 'whatever1';
insert into @vars(loopcntr, value)
select 2, 'whatever2';
select value
from @vars
where loopcntr = 2
select
'
select [' + select value from @varswhere loopcntr = 1 + '],[' + select value from @varswhere loopcntr = 1 + ']
'
I want to be the very best
Like no one ever was
April 28, 2021 at 8:32 am
That does not look feasible to me.
It would be helpful if you could explain the background and reasons for trying to do this – there may be some other way of achieving your aim, which you have not yet considered.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 28, 2021 at 8:44 am
That does not look feasible to me.
It would be helpful if you could explain the background and reasons for trying to do this – there may be some other way of achieving your aim, which you have not yet considered.
I am experimenting to create queries based on a dictionary. So getting Table Name, Column Name, Join Column Names etc.
My problem is that depending on the input i have a varying number of tables that need to be joined , varied number of columns for each join, etc
what i need/my goal is to create a dynamic string that has a variable length and therefore has a variable number of required variables inbetween. With the problem that all the values can not be stored in one variable because all the values needed are all needed at the same time.
The only thing i could somewhat think of would be creating the string step by step by only replacing 1 variable at a time.
I want to be the very best
Like no one ever was
April 28, 2021 at 8:50 am
What is the source data for this? Where do these variables come from? What will the 'dynamic string' be used for, once it has been created?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 28, 2021 at 8:51 am
What is the source data for this? Where do these variables come from? What will the 'dynamic string' be used for, once it has been created?
I am experimenting to create queries based on a dictionary. So getting Table Name, Column Name, Join Column Names etc.
My problem is that depending on the input i have a varying number of tables that need to be joined , varied number of columns for each join, etc
I want to be the very best
Like no one ever was
April 28, 2021 at 8:54 am
Why not use a table variable instead? Then you have have any number of tables, each store as a separate row, in your variable? Though, this does still sounds a quite like an XY Problem to me. If you have an indeterminate amount of tables to JOIN to, sounds like you have a possible design flaw.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 28, 2021 at 8:58 am
Phil Parkin wrote:What is the source data for this? Where do these variables come from? What will the 'dynamic string' be used for, once it has been created?
I am experimenting to create queries based on a dictionary. So getting Table Name, Column Name, Join Column Names etc.
My problem is that depending on the input i have a varying number of tables that need to be joined , varied number of columns for each join, etc
It will be difficult for anyone to suggest a solution, based on such an abstract description. Perhaps you could provide a couple of scenarios, with DDL, sample data and desired results?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 28, 2021 at 10:09 am
ktflash wrote:Phil Parkin wrote:What is the source data for this? Where do these variables come from? What will the 'dynamic string' be used for, once it has been created?
I am experimenting to create queries based on a dictionary. So getting Table Name, Column Name, Join Column Names etc.
My problem is that depending on the input i have a varying number of tables that need to be joined , varied number of columns for each join, etc
It will be difficult for anyone to suggest a solution, based on such an abstract description. Perhaps you could provide a couple of scenarios, with DDL, sample data and desired results?
This is just the beginning of my work, so theres obvious design flaws in structure like the dictionary etc but thats not the point im trying to get across, my point is at the very botto, having a query that produces N rows and placing each value in a different variable.
Obviously im open suggestions that use a different approach.
--####################################################
--Random Query im trying to recreate using Metadata
--####################################################
select tsp.c2, tlj1.c2
from dbo.table_starting_point tsp
left join dbo.table_leftjoined_1 tlj1 on tsp.c1 = tlj1.c1
--Goal: replace every non static part with a variable value that can be queried by regular SQL
--> this works in a static way, query every variable name value and replace it in the string, meh ok
--> am aware that the raw string is static but as i said im just at the beginning of what i am doing
DECLARE @Base_Alias nvarchar(200) = (select ....)
DECLARE @Base_Resultcolumn1 nvarchar(200) = (select ....)
...
select
'
select [' + @Base_Alias + '].[' + @Base_Resultcolumn1 + '],[' + @Join1_Alias + '].[' + @Join1_Resultcolumn1 + ']
from [' + @Base_Schema + '].[' + @Base_Table + '] [' + @Base_Alias + ']
left join [' + @Join1_Schema + '].[' + @Join1_Table + '] [' + @Join1_Alias + '] on [' + @Base_Alias + '].[' + @Join1_Column_10 + '] = [' + @Join1_Alias + '].[' + @Join1_Column_11 + ']
'
--####################################################
--Dictionary Metadata
--####################################################
create table #Dict (
Basetable nvarchar(30)
,Base_Schema nvarchar(30)
,Base_alias nvarchar(30)
,Join_Schema nvarchar(30)
,Join_Table nvarchar(30)
,Join_Alias nvarchar(30)
,Join_Column_base nvarchar(30)
,Join_Column_left nvarchar(30)
,Lookup_column nvarchar(30)
)
insert into #Dict
values
('table_starting_point','dbo','tsp','dbo','table_leftjoined_1','lj1','Match_2_ID','Match_2_ID','Result_Column')
,('table_starting_point','dbo','tsp','dbo','table_leftjoined_1','lj1','Match_3_ID','Match_3_ID','Result_Column')
--####################################################
--Depricated version focusing on only columns since the process is the same, just the variablename and query is different
--Based on this Metadata(or the indiviudal tables) i get my variable values
--####################################################
select *
from #Dict
--Its just 1 1 basetable, but this would be the entry point input if there were more datasets
DECLARE @Base_Alias nvarchar(200) = 'table_starting_point'
--Query that gives me ALL the Values i need for the names of the join columns i have
--this query result defines the number of variables i need --> 2
select Join_column_Base, Counting = ROW_NUMBER() OVER(ORDER BY Join_column_Base)
from (
select distinct Join_column_Base
from #Dict
where Basetable = @Base_Alias
) kek
--In this Example its 2: Match_2_ID/Match_3_ID
-->what i need is 2 variables holding 2 different values at the same time
-->now its known that theres 2 columns needed, holding 2 different values
--####################################################
--Version static:
--####################################################
DECLARE @Join_Column_base_1 nvarchar(200) = 'Match_2_ID' --(select ....)
DECLARE @Join_Column_base_2 nvarchar(200) = 'Match_3_ID' --(select ....)
select
'
select [' + @Join_Column_base_1 + '],[' + @Join_Column_base_2 + ']
'
--####################################################
--Version dynamic --> GIVEN the raw string that only needs variable values
--####################################################
--Using the distinct query i know the number of "@Join_Column_base_N" variables i need and variable values
DECLARE @Base_Alias nvarchar(200) = 'table_starting_point' --> Input, here static
select Join_column_Base, Counting = ROW_NUMBER() OVER(ORDER BY Join_column_Base)
from (
select distinct Join_column_Base
from #Dict
where Basetable = @Base_Alias
) kek
--Now i need something like this to create 2 variables and set their values both based on the query above
DECLARE @counting int = 1
WHILE (@counting < 3)
BEGIN
DECLARE @Join_Column_base_1+@counting NVARCHAR(200)
SET @counting = @counting +1
END
I want to be the very best
Like no one ever was
April 28, 2021 at 11:21 am
FYI, syntax like '[' + @Base_Alias + ']' is not safe from injection. A value with just a single ] character in it would easily circumvent your attempt to stop the injection. If you need to properly quote dynamic objects, use QUOTENAME.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 29, 2021 at 2:22 pm
As a random thought, IF your variables are coming from a 100% safe place OR you restrict the stored procedure so that only DBA's can run it, what if you made your input variables into a single, long string?
What I mean is instead of this:
select
'
select [' + @Join_Column_base_1 + '],[' + @Join_Column_base_2 + ']
'
You have:
select
'
select ' + @Join_Column_base
And @Join_Column_base would be a result like '[Column1],[Column2],[Column3]'? This way, no matter how many columns you got back, the query would work.
The DOWNSIDE to this is that, like Thom A said, you are vulnerable to SQL injection. So you would want to do something to ensure that the data was sanitized which would be incredibly tricky.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply