Archive for the ‘SQLServer’ Category

;MERGE RUO_ITEM AS rsRUO
USING @ruoItems AS updatedRUO
ON (rsRUO .FieldId = updatedRUO.FieldId )
 
WHEN MATCHED THEN
UPDATE
SET
      rsRUO .EntityType = updatedRUO.EntityType ,
      rsRUO .MasterEntityTypeId = updatedRUO.MasterEntityTypeId ,
      rsRUO .FieldValue = updatedRUO.FieldValue ,
      rsRUO .RetailElementId = updatedRUO.RetailElementId ,
      rsRUO .FieldName = updatedRUO.FieldName ,
      rsRUO .ValueSeq = updatedRUO.ValueSeq ,
      rsRUO .CreateDatetime = updatedRUO.CreateDatetime ,
      rsRUO .ChangedDatetime = updatedRUO.ChangedDatetime ,
      rsRUO .RowStatus = updatedRUO.RowStatus ,
      rsRUO .ChangedById = updatedRUO.ChangedById
 
WHEN NOT MATCHED THEN
INSERT (EntityType ,EntityTypeId, MasterEntityTypeId,RowSeq , RetailElementId,FieldValue, FieldName,ValueSeq , CreateDatetime,ChangedDatetime, RowStatus,ChangedById )
VALUES(updatedRUO .EntityType, updatedRUO.EntityTypeId ,updatedRUO. MasterEntityTypeId,updatedRUO .RowSeq, updatedRUO.RetailElementId, updatedRUO.FieldValue ,updatedRUO. FieldName,updatedRUO .ValueSeq, updatedRUO.CreateDatetime, updatedRUO.ChangedDatetime ,updatedRUO. RowStatus,updatedRUO .ChangedById)
output
            inserted .EntityType,     
            inserted .EntityTypeId,     
            inserted .RowSeq,    
            inserted .RetailElementId ,    
            inserted .FieldValue ,     
            inserted .FieldName ,     
            inserted .ValueSeq ,     
            inserted .CreateDatetime ,     
            inserted .ChangedDatetime ,    
            inserted .RowStatus,
            inserted .ChangedById
 into @tmpOutputTable ( 
                        EntityType ,    
                        EntityTypeId ,    
                        RowSeq ,   
                        RetailElementId ,
                        FieldValue ,    
                        FieldName ,    
                        ValueSeq ,    
                        CreateDatetime , 
                        ChangedDatetime ,
                        RowStatus, changedbyid)
declare @tablename nvarchar (100)
set @tablename = ‘party’
 
declare @query nvarchar (1000)
set @query= ‘select top 10 * from ‘ + @tablename
exec (@query )
— Get the dynamic query value assigned to a outer variable
DECLARE @RsDataSQL nvarchar(2000)=‘select top 1 @Rstable2 = ”RS_” + rc.TablePrefix + ”_” + rf.TablePrefix + ”_EventType”’
       +‘ from RControl rc 
      inner join Form rf  on rf.FormId=rc.FormId
      where rf.partyid = 49310′            
 
declare @lrstable nvarchar (500) =
exec sp_executesql @RsDataSQL, N’@Rstable2 nvarchar(500) out’, @lrstable out
 
select @lrstable
— another example
declare @tablename nvarchar (100)
set @tablename = ‘party’
 
declare @query nvarchar (1000)
set @query= ‘select top 1 @partyid = partyid from ‘ + @tablename
 
declare @partyid1 int =
exec sp_executesql @query, N’@partyid int out’, @partyid1 out
 
select @partyid1
SELECT STUFF(
(SELECT ‘,’ + s.PerfIndDesc
FROM table s
ORDER BY s.id
FOR XML PATH( )),1 ,1, )
GO
;with a(
rcontrolid,logoname
)as (
select top 20 RControlId,LogoName  from RControl),
b(
RowNumber,rcontrolid ,logoname
)AS ( SELECT ROW_NUMBER () OVER (ORDER BY RControlId ASC ) AS RowNumber,* FROM a)
select * from b where RowNumber between 2 and 6
IFNOTEXISTS(SELECT*FROM sys.types st JOIN sys.schemas ss ON st.schema_id= ss.schema_id WHERE st.name =N’IdVal’ AND ss.name =N’dbo’)
CREATE TYPE [dbo].[IdVal] AS TABLE (
      [Id] [int] NOT NULL,
      [Val] [nvarchar] (100) NOT NULL
)
GO
/*
declare @Table dbo.IdVal
insert into @Table
      select 1,’val1′
            union
      select 2,’val2′
select * from @Table
exec somesp @TableInput=@Table
CREATE PROCEDURE somesp   
@IdVal IdVal READONLY
AS
……
 
*/

https://www.red-gate.com/products/sql-development/sql-search/

SQL Search – quickly find SQL in SSMS

  •     Find fragments of SQL text within stored procedures, functions, views and more
  •     Quickly navigate to objects wherever they happen to be on a server
  •     Find all references to an objec
  • SQL Search is an add-in for SQL Server Management Studio that lets you quickly search for SQL across your databases.

http://www.red-gate.com/products/sql-development/sql-search/