Usage Of Count() Function Inside Sql Transaction
May 9, 2007Please find my second post in this thread.
Can anyone help me in sorting out the problem and let me know what might be the reason.
Thanks & Regards
Pradeep M V
ADVERTISEMENT
Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
Aug 6, 2006With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
Usage Of Global Variables Inside SQL Task
Mar 11, 2008I've been looking around but haven't yet found the syntax for usage of global variables in an SQL Task.
I've set the global variable Id (see code below):
if (select field from table where id = @[User::id]) is null
select top 1 1 as response from table
else select top 1 0 as response from table
My objective with it is to set another global variable (@isNull). Supposably, when the selection returns null, I should set the variable to null, I did it by using the selections and mapping the response to that variable (is ther a better way to do so?).
When I try to execute this, it says the variable has not been defined.
Here is the error:
Error: Must declare the variable '@'.
I've also tryed it withou the brackets and the User:: thing in the beggining, (@id directly) and here is the response:
Error: Must declare the variable '@id'.
How should I access the global variables in the SQL code?
(BTW, I've checked the field in execution time and it is set to 23, the correct Id, so the block that preceedes this one is working properly)
Thanks,
Analysis :: Count Function Taking More Time To Get Count From Parent Child Dimension?
May 25, 2015below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
How To Count Usage Of Store Procedure?
Mar 26, 2001Hello .
How to count how many time and who executed stored procedure using Sql agent or system tables
How To Count # Of Occurrences Of A Character Inside A String?
Jan 24, 2008I have a string of characters in my data flow and I need to add a derived column showing the # of times a certain character appears within that string. For example, my string in the data flow is:
NNNNNRJKSURNNNEJNNNN
Now I need to count the number of "N"s in that column. From the example above, I should get the integer 12, and that would be the value of my derived column. Any ideas?
How To Count The Tasks Inside A Package Programmatically?
May 7, 2007Hi there,
I'm trying to keep track of the ETL process inserting/updating a row in one table for each package that finish in my ETL process when executing. So far, I created a Script task that increments by one a variable (counter) and then open a connection to my database an insert/update my table. What I want to see is Step 1/30, Step 2/30 and so on. Right know I can display Step 1, Step 2 but how can I get the overall number of tasks within a package?
Thanks in advance for any help you can give me.
Sincerely,
Ricardo
Transaction Inside Sp_executesql
Sep 7, 2006Hi to all,Probably I'm just doing something stupid, but I would like you to tellme that (if it is so), and point the solution.There ist the thing:I' having a sp, where I call other sp inside.The only problem is, the name of this inside sp is builded variously,and executed over sp_executesql:create pprocedure major_sp@prm_outer_1 varchar(1),@prm_outer_2 varchar(2)assome codingset @nvar_stmtStr = N'exec @int_exRetCode = test_sp_' + @prm_outer_1 +@prm_outer_2set @nvar_stmtStr = @nvar_stmtStr + ' @prm_1, @prm_2, @prm_3, @prm_4output'set @nvar_prmStr = N'@prm_1nvarchar(128), ' +N'@prm_2nvarchar(128), ' +N'@prm_3nvarchar(4000), ' +N'@int_exRetCodeint output, ' +N'@prm_4varchar(64) output'exec sp_executesql @nvar_stmtStr,@nvar_prmStr,@prm_1,@prm_2,@prm_3,@int_exRetCode = @int_exRetCode output,@prm_4 = @prm_4 outputNow the issue is, I've transactions inside test_sp_11 lets say wherethe 11 is @prm_outer_1 + @prm_outer_2.These procedures are existing inside database, but are called dynamiclydepending of the parameters.The problem is, when I call the specified sp directly, the rollbacktransaction is working without any problem.Inside this procedures test_sp_xx, is a call of another sp (lets sayinside_sp).There is a transaction included.When it is called over major_sp, then the rollback is not performedbecause of error:Server: Msg 6401, Level 16, State 1, Procedure inside_sp, Line 54Cannot roll back transactio_bubu. No transaction or savepoint of thatname was found.The funniest way is, if there is no error inside, the commit is workingwithout any problem!The question is majory (because I'm almost sure, that this is anissue): is it possible, to have atransaction inside dynamicly called sp over sp_executesql?If ok to do that?Thank's in advanceMatik
View 1 Replies View RelatedUsing Sp_droprolemember Inside A Transaction
Oct 1, 2007I have a trigger which requires dropping a member from a role and includes user transactions. However you cannot call sp_droprolemember from within a user transaction. Looking at the code for the procedure gives me a line
quote:
exec %%Owner(Name = @membername).SetRoleMember(RoleID = @roluid, IsMember = 0)
I can find no documentation on SetRoleMember or %%owner and attempts to run this line as an exec statement fails with "syntax error near '%'"
Nor can I find any way of dropping a member from a role using T-SQL or DDL constructs.
How do I drop a member from a role using T-SQL code and avoiding sp_droprolemember? And where do I find information about the %%Owner construct?
Problem With Transaction Inside Loop
May 6, 2008Hi,
When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.
Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest
Please let me know the reason for this.
Thanks in advance
Regards,
K. Manivannan
Usage Of E.g. Max(Date) Aggregat Function
Jul 20, 2005Hi, I´ve searching for an reuseable SQL-Statement for follwoingsituation:Having e.g. a table tblAdress with some fields like persons full name(as Name), City, Birthdate, Street and so on.Now I will find e.g. all oldest persons for every City. No problemwith:select name, city, max(birthdate) group by name, cityBut if I also want to have the streetname for the oldest person, Ihave a problem cause I can´t include the street into the selectstatement like this:select name, streetname, country, max(birthdate)group by name, countryThis statement is not correct cause streetname is not included ingroup by. If I include the street, I´ve got the oldest persons percity and street. This is not what I want to have. Remember I want theoldest person in City and after that therefore the street ... and thisin a single sql statement. It´s only an example, but I think it´s astandard sql statement to retrieve information like this one forincluded aggregate functions. I spend hours on internet searching andmany self made test ... without any solution. Does anyone has astandard solution for this situation ?regardsHenri
View 5 Replies View RelatedDB Engine :: Transaction Log Space Usage On TempDB Ever Increasing
Jun 15, 2015today I've put in production a big database accessed by 200 concurrent users, this database has READ_COMMITTED_SNAPHOT set to ON.I know that RCSI set to ON is very aggressive on tempDB so I'm monitoring it.I've noticed that the Transaction log space usage (%) on TempDB is slowly but ever increasing, I mean in the last 24 hours I've started from a 99% space free, now we are 37% space free...is it normal? TempDB log is 35GB in size.
View 6 Replies View RelatedFunction Inside The Procedure
Jan 29, 2008i have created the table empid,empname,dob,salary for the insert i need to create procedure of insert empid,empname,dob,salary i got the function for autogenerated number for empid
mmy question how to put function inside the procedure?
my function below,pls guide me its very urgent.
create function NextCustomerNumber()
returns char(5)
as
begin
declare @lastval char(5)
set @lastval = (select max(customerNumber) from Customers)
if @lastval is null set @lastval = 'C0001'
declare @i int
set @i = right(@lastval,4) + 1
return 'C' + right('000' + convert(varchar(10),@i),4)
end
Desikankannan
Problem Retrieving @@Identity When Inside A Transaction.
Aug 22, 2005I'm using transactions with my SqlConnection ( sqlConnection.BeginTransaction() ).
I do an Insert into my User table, and then subsequently use the
generated identity ( via @@identity ) to make an insert into another
table. The foreign key relationship is dependant on the generated
identity. For some reason with transactions, I can't retrieve the
insert identity (it always returns as 1). However, I need the
inserts to be under a transaction so that one cannot succeed while the
other fails. Does anyone know a way around this problem?
So heres a simplefied explanation of what I'm trying to do:
Begin Transaction
Insert into User Table
Retrieve Inserted Identity (userID) via @@Identity
Insert into UserContact table, using userID as the foreign key.
Commit Transaction.
Because of the transaction, userID is 1, therefore an insert cannot be
made into the UserContact table because of the foreign key constraint.
I need this to be a transaction in case one or the other fails. Any ideas??
Checking For @@ERROR After A SELECT Inside A Transaction?
Feb 26, 2006Is it normal practice to check for @@ERROR after a SELECT statement that retrieves data from a table OR we should only check for @@ERROR after a DELETE/INSERT/UPDATE type of statement? The SQL statement is inside a transaction.
View 1 Replies View RelatedSend Mail Task Inside Transaction?
Oct 9, 2007Hi,
Can we include a Send Mail Task inside a transaction?
We intend to callback a sent email in case there is an error in the subsequent task, if it is possible.
Thanks and Regards,
B@ns.
Transaction Inside A Data Flow Task
Oct 30, 2006We are designing an ETL solution for a BI project using SSIS.
We need to load a dimension table from a source DB into the DW; inside the DW there are two tables for each source dimension table: a current dimension table and a storical dimension table.
The source table includes technical columns that indicate if each record was processed correctly by ETL procedures.
Each row in the source table can be a new record, or an exisisting one. If it's a new record, a corresponding new record should be inserted into the current dimension table of the DW; if it's an exisisting one, a new record should be inserted in the storical dimension table and the existing record in the current dimension table should be updated.
Furthermore for each record we need to update the source table with an error code. If the record was processed with succes the code is zero, otherwise it contains an error code.
I try to use a single data flow task, using the 'ole db command trasformation' task for managing update and 'ole db destination' task for managing insert.
The problem is that some insert and update should be executed inside a single transaction, for each record; for example if the source contains a new record I should insert the record in the current dimension table and update the source record with error code zero if every think goes fine.
There is some way to group task in the data flow pane in a single transaction ?
There is a better way to do that ?
Cosimo
Slow Execution Of Queries Inside Transaction
Apr 11, 2006
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everything works great.
Can you help me to find out the problem?
Thanks,
Laura
Function Inside A Select Statement
Aug 23, 1999Can I write a function inside a Select statement in sql server 7.0
If so HOW ?
Manish Mehta
Dynamic Sql Inside Function And Return Value
Apr 10, 2007Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working...
Thanks
______________________________________________________________________
set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey
from rt_'+@platform+'_CallLegs
where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5))
exec @sql
return @CallLegKey
How To Read Data From Remote Server Inside A Transaction?
Nov 23, 2005Hello, everyone:
I have a local transaction,
BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT
View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:
Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].
It looks like remote server is not available inside the local transaction. How to handle that?
Thanks
ZYT
CREATE FULLTEXT CATALOG Inside A User Transaction.
Jan 15, 2007hi:
I try to create full text for new created tables.
Since all new created tables will have same columns with different table name.
After I run the stored procedure to create table, after i got the new table name, I would like to create full-text on that table in the DDL triger.
But I got error like this:
CREATE FULLTEXT CATALOG statement cannot be used inside a user transaction.
Any one has idea how to deal with it?
Thanks
How To Create A Second Independent Transaction Inside A CLR Stored Procedure?
Nov 29, 2005I use the context connection for the "normal" work in the CLR procedure.
View 7 Replies View RelatedProblems With ABS() Function Inside Sub Query (within WHERE-Clause)
May 28, 2008Hello,
when trying to execute the following query with SQL CE 3.1 and OLEDB on WM2003:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND ABS(Measurement_Value) BETWEEN ? AND ?
GROUP BY Panel_Position)
i get this error returned:
0x80040E1DL -- DB_E_UNSUPPORTEDCONVERSION -- Requested conversion is not supported.
I don't know where inside the sql string a conversion is necessary/fails.
Surprisingsly when i modify the sql statement a little, it is executed WITHOUT ERRORS:
SELECT C.Panel_Id, C.Panel_Tier, C.Panel_Type, C.Panel_No, C.Panel_Position
FROM tblMeasurements AS A, tblAssignment_Ant_Pan AS B, tblPanels AS C
WHERE (A.Measurement_No=?) AND (A.Antenna_No = B.Antenna_No) AND (B.Panel_Id = C.Panel_Id) AND C.Panel_Position in
(SELECT Panel_Position FROM tblMeasurement_Results
WHERE (Measurement_No=?) AND Measurement_Value BETWEEN ? AND ?
GROUP BY Panel_Position)
The only difference between the 2 statements is the ABS() function inside the sub query.
More surprisingly, with the query analyser on the PDA i can execute both statements fine. I am absolutely confused now where i have to search for the mistake.
I would appreciate it very much if someone out there knows an answer or a hint and could tell me.
With kind regards,
Andre
Problem Returning A Timestamp Column Inside An TSQL Transaction
Jan 15, 2007I cannot manage to fetch the new timestamp value inside a TSQL Transaction. I have tried to Select "@LastChanged" before committing the transaction and after committing the transaction. A TimestampCheck variable is used to get the timestamp value of the Custom Business Object. It is checked against the row updating to see if they match. If they do, the Update begins as a Transaction. I send @LastChanged (timestamp) and an InputOutput param, But I also have the same problem sending in a dedicated timestamp param ("@NewLastChanged"): 1 select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId
2
3 if @TimestampCheck is null
4 begin
5 RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
6 return -1
7 end
8 else if @TimestampCheck <> @LastChanged
9 begin
10 RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
11 return -1
12 end
13
14
15 Begin Tran Address
16
17 Update ADD_Address
18 set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
19 where AddressId= @AddressId
20
21 select @error_code = @@ERROR, @AddressId= scope_identity()
22
23 if @error_code = 0
24 begin
25 commit tran Address
26
27 select @LastChanged = LastChanged
28 from ADD_Address
29 where AddressId = @AddressId
30
31 if @LastChanged is null
32 begin
33 RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
34 return -1
35 end
36 if @LastChanged = @TimestampCheck
37 begin
38 RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
39 return -1
40 end
41 return 0I do not have this problem if I do not use a TSQL Transaction. Is there a way to capture the new timestamp inside a Transaction, or have I missed something?Thank you,jspurlin
Newbe Question: Calling Function Inside Select
Jul 20, 2005Hi!I have a scalar function that returns integer:xview (int)Now, I'm trying to build a procedure that has the following selectinside:select atr1, xview(atr2)from tablenameBut, I get the 'Invalid name' error when I try to execute thatprocedure.If I got it right, I must use user.fn_name() syntax, but I cannot usedbo.xview() inside my procedure since it means xview will always beexecuted as dbo, which is unaccaptable.I'm a bit confused, so any hint is very welcomed.Thanks!Mario.
View 4 Replies View RelatedHelp With Solution For Use Order BY Inside Function And VIEW On The Sql Server
Apr 8, 2008help with solution for use order BY inside function and VIEW on the sql server
i explain the problem
i am a web developer in asp page
i BUY an DATA grid object for my web site
the problem in this object i can use only ONE field to use ORDER BY
can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ?
so whan i do
SELECT *
FROM dbo.tb_pivot_big
the sql server ORDER for ME the result and run this (inside the sql server )
Code Snippet
SELECT *
FROM dbo.tb_pivot_big
ORDER BY new_unit, Fname ASC, val_orginal desc
TNX for help
Permission-problem On Simple Query Inside CLR Function
Aug 17, 2007I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.
Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
Transact SQL :: Cannot Perform Shrink File Operation Inside User Transaction
Sep 18, 2015I am getting an error about "Cannot perform a shrinkfile operation inside a user transaction", but I don't have a shrinkfile command in my procedure. Â Does SQL hang on to that command if it was received earlier in a different procedure?
View 5 Replies View RelatedDATEDIFF Function Dosen't Return Anything If Used Inside ASPX Page!!
Feb 12, 2008Dear all, I have the Following Query:SELECT DATEDIFF(day, GETDATE(), RECENT_RESERVATION)AS Expr1,RECENT_RESERVATION FROM EMP WHERE SUN=empName;when i run it inside the query analyzer, it returns two columns. but if run it inside The ASPX page it retuns only one column wich is RECENT_RESERVATION date.Note: i am using one methode that takes care of reading from SQL and assigning the result into an array, it works fine everywhere, but with this query it dosen't work. Any suggestions??
View 6 Replies View RelatedCalling Managed CLR Procedure From Inside User Defined Function -- How To ?
May 15, 2008I have several UDFs created. Inside one of the UDFs I need to execute a dynamic SQL statement and then take that result and do something else with it, before returning the final value.
I know you can not execute a stored proce from inside a function. I also know you can not use the EXEC statement.
I did read that you could use an external stored procedure and/or managed CLR procedures inside a function.
I have created a managed procedure CLR (C#) that simply executes a passed statemetn and returns the value to the calling routine. I have this all coded and is working fine.
However, I am struggling with knowing how to call this CLR procedure from inside my function, seeing how I can not use EXEC statement.
Any advice on how to do this?
Thanks,
Bruce
How To Determine, Inside A Function, If A Linked-server-query Returned Results
Feb 13, 2004Hi, have configured an ODBC linked server for an Adaptive Server Anywhere (ASA6.0) database.
I have to write a function (not a procedure) that receives a number (@Code) and returns 1 if it was found on a table in the linked server, or 0 if not. Looks very simple...
One problem, is that the queries on a linked-server must be made through the OPENQUERY statement, which doesen't support dynamic parameters. I've solved this making the whole query a string, and executing it, something like this:
SET @SQL='SELECT * FROM OPENQUERY(CAT_ASA, ''SELECT code FROM countries WHERE code=' + @Code + ''')'
EXEC sp_executesql @SQL
(CAT_ASA is the linked-server's name)
Then, i would use @@ROWCOUNT to determine if the code exists or not. But before this, a problem appears: sp_executesql is not allowed within a function (only extended procedures are allowed).
Does somebody know how to make what i want?? I prefer to avoid using temporary tables.
Thanks!
Transact SQL :: Creating Comma Separated Value Logic Not Working Inside Function?
Jul 16, 2015I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue
AS
(
declare @ConcatenatedString NVARCHAR(MAX)
select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR)
from
(
select 1 Number
union
select 2 Number
union
select 3 Number
)rslt
return @ConcatenatedString
)