June 24, 2016 at 4:01 pm
Jeff Moden (6/23/2016)
curious_sqldba (6/22/2016)
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.
Two way street here... what is the solution that you did go for? It might help US in the future.
Absolutely :). It was very simple, as Vijay mentioned i used coalesce function.
DECLARE
@database2 NVARCHAR(100)
SET @database2 = NULL
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' )
,
( 'C' )
select @database2 = COALESCE(@database2 + ',', ' ')
+ DBname
from @dbs
select @database2
June 24, 2016 at 4:46 pm
curious_sqldba (6/24/2016)
Jeff Moden (6/23/2016)
curious_sqldba (6/22/2016)
Sean Lange (6/22/2016)
curious_sqldba (6/22/2016)
Thanks. This is just displaying, i want to store the data in the variable and then use the variable different places.Do you know how to use a select statement to populate a variable? It is no different here.
However, I have a feeling that you are about to embark down a path of no return. The fact that you are creating a delimited list in a variable leads to believe you are hoping to use that in where clauses. If that is the case you are going to end up in another pickle....variables don't work like that.
Perhaps you can explain what you are really trying to accomplish and we can help you find a better approach.
You are right i did horrible job in explaining my question :(.
The above query works but i went with a different solution.
Two way street here... what is the solution that you did go for? It might help US in the future.
Absolutely :). It was very simple, as Vijay mentioned i used coalesce function.
DECLARE
@database2 NVARCHAR(100)
SET @database2 = NULL
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' )
,
( 'C' )
select @database2 = COALESCE(@database2 + ',', ' ')
+ DBname
from @dbs
select @database2
Thanks.
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
As a bit of a sidebar, yes, I know it's example code but you should get into the habit of adding semi-colons. Not having them has been deprecated so you might as well get into the habit. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
June 29, 2016 at 10:33 pm
I hope this is you are expecting........
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
DECLARE @database varchar(100)
DECLARE @database1 varchar(100)=''
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' ),
( 'C' )
DECLARE databases CURSOR
FOR
SELECT DBname
FROM @dbs
OPEN databases
FETCH NEXT FROM databases INTO @database
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
set @database1 = @database1+','+@database
FETCH NEXT FROM databases INTO @database
END
CLOSE databases
DEALLOCATE databases
set @database = substring(@database1,2,len(@database1))
select @database
June 30, 2016 at 3:42 pm
ammit.it2006 (6/29/2016)
I hope this is you are expecting........
DECLARE @dbs TABLE ( DBname NVARCHAR(100) )
DECLARE @database varchar(100)
DECLARE @database1 varchar(100)=''
INSERT INTO @dbs
VALUES ( 'A' ),
( 'B' ),
( 'C' )
DECLARE databases CURSOR
FOR
SELECT DBname
FROM @dbs
OPEN databases
FETCH NEXT FROM databases INTO @database
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
set @database1 = @database1+','+@database
FETCH NEXT FROM databases INTO @database
END
CLOSE databases
DEALLOCATE databases
set @database = substring(@database1,2,len(@database1))
select @database
Ignoring the implicit conversions that will occur in that code and ignoring that the incredibly small row counts make it so that performance can essentially not be a concern, why do you think that a cursor is necessary for this?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2016 at 4:24 pm
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
June 30, 2016 at 4:46 pm
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
And there is always an exception, such as actually using Unicode constants. If a column or variable is defined as an NVARCHAR data type there is NOTHING wrong with prepending the string with an N to ensure that it is interpreted properly.
Using semicolons as a begininator is one of my pet peeves, but so is telling people they should never use the N'' format for string constants.
June 30, 2016 at 8:46 pm
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
Very good point/tip especially as far as the VALUES statement in the code above and especially in WHERE clauses. But I have to disagree when you're doing something like the SELECT/COALESCE variable overlay or other string calculation. In this case, it's not so bad because you're not likely to have thousands of databases and certainly no need for more than one return. But, if you're doing some string calculation over millions of rows, the relatively insignificant implicit conversion can begin to be one of those things that can add up, especially if you need to do the calculations over dozens of columns.
Like Granny used to say, "Mind the pennies and the dollars will take care of themselves". 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 30, 2016 at 9:18 pm
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
You should always remember that your would is limited by your knowledge about the world.
And apparently your knowledge is far from being universal.
And the rules which work in your world may be not applicable for others.
This is an international forum.
People who read it use to have literal constants not only in English.
Here how your rule "works" for them:
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES ('?'),('?'),('?')
;
SELECT @database2 = ISNULL(@database2 + ',', ' ') + DBname
FROM @dbs
;
SELECT @database2
Output:
?,?,?
_____________
Code for TallyGenerator
July 1, 2016 at 7:36 am
Sergiy (6/30/2016)
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
You should always remember that your would is limited by your knowledge about the world.
And apparently your knowledge is far from being universal.
And the rules which work in your world may be not applicable for others.
This is an international forum.
People who read it use to have literal constants not only in English.
Here how your rule "works" for them:
DECLARE @database2 NVARCHAR(100);SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES ('?'),('?'),('?')
;
SELECT @database2 = ISNULL(@database2 + ',', ' ') + DBname
FROM @dbs
;
SELECT @database2
Output:
?,?,?
Wowwww. It does that even with the original Coalesce code. Amazing. Today I learned two really valuable lessons... 1) my "universe" is a whole lot smaller than I thought and 2) there are "black holes" that I'd never seen before.
Thanks, Sergiy. Really good stuff.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2016 at 8:31 am
Sergiy (6/30/2016)
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
You should always remember that your would is limited by your knowledge about the world.
And apparently your knowledge is far from being universal.
And the rules which work in your world may be not applicable for others.
This is an international forum.
People who read it use to have literal constants not only in English.
Here how your rule "works" for them:
DECLARE @database2 NVARCHAR(100);SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES ('?'),('?'),('?')
;
SELECT @database2 = ISNULL(@database2 + ',', ' ') + DBname
FROM @dbs
;
SELECT @database2
Output:
?,?,?
I also rely on common sense, which is apparently less universal than I thought. Of course if you're using characters that require unicode for them to be accurately represented, you should always code them using unicode literals. We are international as well, but we've still changed unicode to non-unicode strings where possible to save overhead.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 1, 2016 at 8:47 am
In case that was missed in translation (!), here it is again:
DECLARE @database2 NVARCHAR(100);
DECLARE @dbs TABLE (DBname NVARCHAR(100));
INSERT INTO @dbs VALUES ('?'),('?'),('?');
SELECT * FROM @dbs
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 1, 2016 at 9:27 am
Agreed... and the catch here is that if this happens, what else could happen? If you use the "N" prefix for the literals whenever NVARCHAR is used, it's pretty much guaranteed not to happen.
If you don't know if NVARCHAR is being used on the column or not, then you haven't done due diligence as a database or other type of Developer.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2016 at 9:39 am
ScottPletcher (7/1/2016)
Sergiy (6/30/2016)
ScottPletcher (6/30/2016)
Jeff Moden (6/24/2016)
Shifting gears a bit and depending on how that's used, it could become a performance problem due to the implicit conversions of the string literals. Since @database2 has been declared as NVARCHAR, the literals should also be made to be NVARCHAR. Like this...
DECLARE @database2 NVARCHAR(100);
SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES (N'A'),(N'B'),(N'C')
;
SELECT @database2 = COALESCE(@database2 + N',', N' ') + DBname
FROM @dbs
;
SELECT @database2
;
I strongly recommend against explicitly making literals nvarchar. The resulting implicit conversion is truly negligible overhead anyway. The real issue is if the column type changes to varchar, because then you explicitly forcing an nvarchar string will force an implicit column conversion, which will be many, many orders of magnitude worse than the implicit conversions of literal strings.
You should always remember that your would is limited by your knowledge about the world.
And apparently your knowledge is far from being universal.
And the rules which work in your world may be not applicable for others.
This is an international forum.
People who read it use to have literal constants not only in English.
Here how your rule "works" for them:
DECLARE @database2 NVARCHAR(100);SET @database2 = NULL
;
DECLARE @dbs TABLE (DBname NVARCHAR(100))
;
INSERT INTO @dbs
VALUES ('?'),('?'),('?')
;
SELECT @database2 = ISNULL(@database2 + ',', ' ') + DBname
FROM @dbs
;
SELECT @database2
Output:
?,?,?I also rely on common sense, which is apparently less universal than I thought. Of course if you're using characters that require unicode for them to be accurately represented, you should always code them using unicode literals. We are international as well, but we've still changed unicode to non-unicode strings where possible to save overhead.
Common sense is a super power and if you have really read most of the posts here (or on any other forums) it is in very short supply. Many of the people out there will take what they read as gospel rather than think things through for themselves.
July 1, 2016 at 9:56 am
Jeff Moden (7/1/2016)
Agreed... and the catch here is that if this happens, what else could happen? If you use the "N" prefix for the literals whenever NVARCHAR is used, it's pretty much guaranteed not to happen.If you don't know if NVARCHAR is being used on the column or not, then you haven't done due diligence as a database or other type of Developer.
I was very explicitly clear that I was talking about literals. If I'm typing a literal, presumably I'd have to know whether that literal required nvarchar or not. If it doesn't, I still say don't explicitly code it unless the code requires it (some system procs require unicode strings specifically, for example), because it can cause performance errors or mismatches but cannot prevent any.
Column data types can change. Yes, there will always be code review required to change a column's data type, but you can often drastically reduce code re-work if you don't needlessly force an explicitly matching data type on literal values.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply