SQL Server 2012 :: STUFF Function With HTML Tag

Jan 6, 2015

I have the following query that supposes to merge multiple result in a single one and put it into a temporary table:

SELECT DISTINCT [AlphaExtension],
STUFF((SELECT A.[NoteText] + '< BR />' FROM #temp A
WHERE A.[AlphaExtension]=B.[AlphaExtension]
FOR XML PATH('')),1,1,'') As [NoteText]
FROM #temp B
GROUP BY [AlphaExtension], [NoteText]

It is working fine unless by a simple detail. If you look at the second line of the query you will see that I am stuffing together a < BR /> tag (break line) because the contents of the field is going to be spitted directly to the screen and I want that the multiple results be displayed in different lines.

OK, the issue is that it is stuffing & lt ; BR / & gt ; instead < BR /> and therefore the browser is displaying the tag instead to break a line.

View 2 Replies


ADVERTISEMENT

Stuff Function Not Working With Left Or Right Join

Jan 1, 2015

I found something very strange...stufff function working with self join but not working with left or right join,. I have a table

**Id name**

1 samar

1 Harry

2 jack

I want the output as

**Id name**

1 samar Harry

2 jack

The below query works fine with self join

Select b.id, stuff ((select ` ` + a.name from #test a where a.id = b.id
for xml path (``)),1,1,``)

From #test b
Group by id

But when i do right join i get error _ invalid object name `b`. ....

Select b.id, stuff ((select ` ` + a.name from #test a
right join b on b.id = a.id for xml path (``),1,1,``)

From #test b
Group by id

View 4 Replies View Related

SQL Server 2012 :: Wrapping Of Text In HTML (XML)

Sep 3, 2015

I am using FOR XML to generate the HTML and following is my query. I am using td { white-space:nowrap;} for the columns to used no wrap. But i want two columns which need to be wrapped. i have tried lots of options.

SET @tableHTML =
N'<H3><font size="4"> Board - Items </font></H3>' +
N'<table border="1" ><font size="1"> <head><style TYPE="text/css"> td { white-space:nowrap;} </style> ' +
N'<tr bgcolor=#4b6c9e><font size="2" color=white><th> ID</th>
<th> A</th>
<th> B </th>

[Code] ...

From the above code i want G and H columns to be wrapped.

View 3 Replies View Related

SQL Server 2012 :: Import Data From HTML Document Into Table

Oct 8, 2014

I have someone who is sending me .htm documents, with a table in them, and I was wondering if there is a way to import the data from those tables into a SQL table, probably using an SSIS Package.

View 4 Replies View Related

SQL Server 2012 :: Converting Query Output To HTML Format

Mar 23, 2015

I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.

The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.

/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Table2HTML] (
@TABLENAME NVARCHAR(500),
@OUTPUT NVARCHAR(MAX) OUTPUT,

[Code] ....

The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.

<style type="text/css">table.gridtable { font-family: verdana,arial,sans-serif; font-size:10px; color:#333333;border-width:1px; border-color: #666666; border-collapse: collapse; } table.gridtable td {border-width: 1px; padding: 8px; border-style: solid; border-color: #666666; background-color: #ffffff;}</style>

If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.

Msg 105, Level 15, State 1, Line 98
Unclosed quotation mark after the character string '</table></body><'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '</table></body><'.

[Code] .....

I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.

View 6 Replies View Related

SQL 2012 :: Send Email In HTML Format

Apr 27, 2015

I wanted to implement the feature to send the email in HTML format from SQL. Which option will be more easy and less resource consumption? Like

sp_send_dbmail or sp_OAMethod or anything else?

View 2 Replies View Related

Newbie - SQL Server Express And Other Stuff

Feb 16, 2008

Hi

View 1 Replies View Related

Simple SQL Server Stuff For An Oracle Person...

Jul 27, 2006

I'm trying to do some basic stuff in SQL Server 2005 that I could do in Oracle.  I'm sure there's a way to do these things, I just don't know how.  My most immediate question is this:
Is there any easy way to run a SQL script and have it spool to HTML tables to a file?  The 'results to text' from SSMS is not adequate for display purposes.  I'm trying to display table/column descriptions.
Beyond that, does anyone know of any good resources that provide an 'Oracle to SQL Server' matrix so I can help figure out how to do some of these things in SQL Server?
Thanks in advance,
Mike

View 1 Replies View Related

Very NEW To This Database Server Stuff... Probably Simple For You, But Hard For Me...

Jan 4, 2008

Hi everyone,

BACKGROUND:
I just set up a network with 2 computers (direct cable network, no routers/hubs), one computer is running Windows 2000 Server and the other is running Windows XP Professional. They both recognize each other and I can access each computer on either computer. The WinXP computer has SQL Server 2005 express edition installed on it.

BASIC PROBLEM:
I am trying to install a database server on my Windows 2000 Server OS computer. This computer is a 350MHz, 256mb RAM computer. How would I go about doing this?

Thanks.

View 11 Replies View Related

SQL Server 2012 :: How To Use MAX Function In Another Database

Nov 30, 2013

I want to use max() function and I want to read the input of this function from another database(its name is exhibitor). like below :

select @LastDate=MAX([exhibitor.dbo.Maintable.LastUpdate])but I have error below

Msg 207, Level 16, State 1, Procedure Exec_List, Line 131
Invalid column name 'exhibitor.dbo.Maintable.LastUpdate'.

View 2 Replies View Related

SQL Server 2012 :: How To Use Scalar Function Without WHILE

Jan 19, 2014

I have a scalar function, which calculates the similarity of two strings. I use the following query, to compare the entries of one table against the value 'Test' and return the entries, which have a value > 50:

;WITH cte1 AS (
SELECT b.FirstName,
(SELECT fn_similarity('Test', b.FirstName)) AS [Value],
b.LastName
FROM [AdventureWorks2012].[Person].[Person] b
)

SELECT *
FROM cte1
WHERE [Value] > 50.00
ORDER BY [Value] DESC

Now I want to use this query against the first 50 entries of the [Person] table, so that the resultset includes all the values of the first 50 persons and the entries, which are similar to them.

At the moment I use a WHILE-loop and write the five single resultsets in a temporary table. Is there another way / a better way, maybe via a join?

View 9 Replies View Related

SQL Server 2012 :: CDC Get All Changes Function Error

Nov 13, 2014

I have setup CDC on 50 tables and then in one SP I’m calling all cdc function like below issue is I'm getting error “an insufficient number of arguments were supplied for the procedure or function cdc.fn_cdc_get_all_changes ... .” as error is not mentioning for which capture instance I'm getting this error so not able to find.

select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old') union all
select * from cdc.fn_cdc_get_all_changes_<capture_instance>(@from_lsn, @to_lsn, 'all update old')

How to find which capture instance is failing?

View 2 Replies View Related

SQL Server 2012 :: Get Sum Multiplied Values From Function

Jan 18, 2014

I have an existing function and need to alter function to give result of the values multipiled until its parent is reached.need two seperate functions for city and amt columns..need to also display the parent-description

--CREATE TABLE
CREATE TABLE [dbo].[CityData](
[Id] [int] NULL,
[ParentID] [int] NULL,
[City] [nchar](20) NULL,
[Location] [nchar](50) NULL,
[Amt] [int] NULL
) ON [PRIMARY]

[code]...

View 8 Replies View Related

SQL Server 2012 :: Getting A Variable Recognized In A Function

Apr 4, 2014

I am having a hard time getting a variable recognized in a function. The variable is not being seen properly in the charindex function.

@ExtType contains = X
@PhoneNo contains = +1 (202) 123-9876 X012

select @intPos = charindex(@ExtType,Upper(@PhoneNo))

View 1 Replies View Related

SQL Server 2012 :: Get Splitted Column Using Function

Apr 21, 2015

I am having staging table with separted by '¯'.I want to split the data with given number .i have given 31 means my main table have 31 column. it should handle the less or more column.

declare @TempTable as Table (Id int identity, sampleData nvarchar(500))
insert into @TempTable (sampleData)
select 'B¯080623719¯¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯Y¯Y¯ ¯' union all
select 'B¯106618392¯¯ ¯ ¯ ¯Y¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯ ¯'

[code]....

View 9 Replies View Related

SQL Server 2012 :: Possible To Tell Whether Function Passed A Value Or Used Default Value

Apr 22, 2015

I have a function that accepts a date parameter and uses getdate() as its default value. If a date is passed in, I'm going to have to find records using the datediff method based on input. If no date is passed, I am going to bypass the datediff logic and search for records based on a column called "is_current" which will reduce the query time.

However, I don't know how to tell if the date value in the function came from an input or was the default.

View 4 Replies View Related

SQL Server 2012 :: Using User Defined Function In View?

Jan 31, 2014

I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.

Function:
-----------------------------------
ALTER FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),

[Code].....

Not sure what I am missing in the above view why it doesn't split the string.

View 8 Replies View Related

SQL Server 2012 :: Window Function On Different Date Ranges?

Feb 5, 2014

I have a question regarding windowing functions. I have a sales order table with the columns "orderid", "customerid", "order_date" and "amount". I use the following query to get the amount of every customer as a additional column:

Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid)
FROM sales_orders

My question is if there is a good way to add another column, which includes the SUM(amount) of the customerid, where the order_date > 2012-01-15 , something like this:

Select customerid,
orderid,
order_date,
amount,
SUM(amount) OVER (PARTITION BY customerid),
SUM(amount) OVER (PARTITION BY customerid WHERE order_date > 2012-01-15)
FROM sales_orders

I know, this is not a valid method, so do you know a way to achieve this? Can I maybe use CROSS APPLY or something like this? I know that I could use a subquery to get this, but is there maybe a way / a better way via window functions?

View 9 Replies View Related

SQL Server 2012 :: Function To Remove Excess Characters

Mar 5, 2014

I am looking for a function or way to return only results which does not include appended characters to order numbers.

For instance, below is a list of order numbers. I only want the order number that is SO-123456

OrderNumbers
SO-123456
SO-123456-01
SO-123456-2
SO-123457
SO-123457-1
SO-123457-02
SO-123458

I would like my query to only show the below results

SO-123456
SO-123457
SO-123458

What functions or query methods could achieve this?

I was hoping for something similar to RTRIM but that is only specific to white space.

View 9 Replies View Related

SQL Server 2012 :: Convert Function Inconsistent With XML Entities?

Aug 27, 2014

I have the following data stored in a column in a table:

£10,000.00 &amp; &apos; &quot; % &lt; &gt; Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones

I query this in a stored procedure using the FOR XML clause and universal table, and store the result in an XML data type variable:

DECLARE@ResultXML
EXECUTE[someStoredProcedure] @Result OUTPUT

This data (as XML) is then written to a file; in order to do this, I CONVERT the data to NVARCHAR since there are unicode characters in the source:

DECLARE@strResultNVARCHAR(MAX)
SET@strResult= CONVERT( NVARCHAR( MAX ), @Result, 1 )

Now this works fine, except on inspection, SQLServer has decided to render the data thus:

£10,000.00 &amp; ' " % &lt; &gt; Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones

Why it has changed the apos and quot entities to the corresponding character but not the other entities is beyond me.

how to preserve XML entities?

View 0 Replies View Related

SQL Server 2012 :: Call A Split Parameter Function

Sep 15, 2014

In t-sql 2012, the followinng sql works fine when I declare @reportID.

IF @reportID <> 0
BEGIN
SELECT 'Students report 1' AS selectRptName, 1 AS rptNumValue
UNION
SELECT 'Students report 2', 2
UNION

[code]...

However when I use the sql above in an ssrs 2012 report, the query does not work since the @reportID parameter can have 0, 1, or up to 200 values.Thus I am thinking of calling the following following function to split out the parameter values:

FUNCTION [dbo].[fn_splitString]
(
@listString VARCHAR(MAX)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
(
SELECT SUBSTRING(l.listString, sn.Num + 1, CHARINDEX(',', l.listString, sn.Num + 1) - sn.Num - 1) _id
FROM (SELECT ',' + LTRIM(RTRIM(@listString)) + ',' AS listString) l
CROSS JOIN dbo.sequenceNumbers sn
WHERE sn.Num < LEN(l.listString)
AND SUBSTRING(l.listString, sn.Num, 1) = ','
)

GO

how to remove the @reportID <> 0 t-sql above and replace by calling the fn_splitString function?

View 2 Replies View Related

SQL Server 2012 :: Create A Function That Take A Value And Run Some Logic And Output The Value?

Feb 20, 2015

I would like to create a function that take a value and run some logic and output the value

I have a table like this

Table A
value
*
001
004.00
3.0
1.22

Logic I want to run is

The value that you are passing is numeric and numeric with only decimal 0 value, and then convert it to integer otherwise leave as it is

So if I run a query something like this

Select value, fn_convertointerger(value) as converted_value from TableA

I will get

Value converted_value
* *
001 1
004.00 4
3.0 3
1.22 1.22
2.02 2.02
4.000 4
Jkil& Jkil&

How can I create a function like this to convert specific numeric value?

View 9 Replies View Related

SQL Server 2012 :: Dynamic Return Type In A Function

Mar 3, 2015

I have created a function that will check whether the data is null or not. If its null then it will display that as No data else it will display the original value. Below is the function

GO
Object: UserDefinedFunction [dbo].[fnchkNull] Script Date: 3/4/2015 12:01:58 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]...

The code is working good. However i want the return type to be dynamic. If the data type supplied is integer then i want to return a integer value like 0 if its null. if the data value is varchar then i want to return 'No Data'.

View 7 Replies View Related

SQL Server 2012 :: Select Query With REPLACE Function?

May 22, 2015

using below code to replace the city names, how to avoid hard coding of city names in below query and get from a table.

select id, city,
LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(city,
'JRK_Ikosium', 'Icosium'), 'JRK_Géryville', 'El_Bayadh'),'JRK_Cirta', 'Constantine'),'JRK_Rusicade', 'Philippeville'),
'JRK_Saldae', 'Bougie')))
New_city_name
from towns

View 3 Replies View Related

SQL Server 2012 :: Using Function In Create Index Statement

Jun 10, 2015

Can we use a sql function() in create index as below is giving error , what would be work around if cannt use the function in below scenario

CREATE NONCLUSTERED INDEX [X_ADDRESS_ADDR1_UPPER] ON [dbo].[ADDRESS]
(
UPPER([ADDR_LINE_1]) ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

View 3 Replies View Related

SQL Server 2012 :: Scalar Function Returning Zero When It Shouldn't

Jun 16, 2015

I have this code:

Declare @sql as varchar(4000)
declare @tbl as varchar(100)
declare @exists as bit
select @tbl = 'ACA_RSF'
select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''
print @sql
exec (@sql)

and it returns 18 million for a record count.I have this scalar returning function, which models the above, and it returns zero:

select dbo.fnGet_Rec_Count('ACA_RSF') as cnt

here is the code:

alter FUNCTION spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN

-- Declare the return variable here

DECLARE @count bigint

-- Add the T-SQL statements to compute the return value here

select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')

-- Return the result of the function

RETURN @count
END
GO

I get zero regardless of where @count is declared as in or bigint.

View 9 Replies View Related

SQL Server 2012 :: String Variables Comparison Function

Aug 10, 2015

What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.

View 9 Replies View Related

SQL Server 2012 :: Why Partition Function Works For Datetime2 But Not For Datetime1

Dec 20, 2013

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime = '2007-09-01 00:00:00.000';
WHILE @i < '2008-10-01 00:00:00.000'
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

[Code] ....

Msg 7705, Level 16, State 2, Line 1
Could not implicitly convert range values type specified at ordinal 1 to partition function parameter type.

However if I change to datetime2 it works

DECLARE @DatePartitionFunction nvarchar(max) = N'CREATE PARTITION FUNCTION DatePartitionFunction (datetime2) AS RANGE RIGHT FOR VALUES (';
DECLARE @i datetime2 = '2007-09-01 00:00:00.000';
WHILE @i < '2008-10-01 00:00:00.000'
BEGIN
SET @DatePartitionFunction += '''' + CAST(@i as nvarchar(10)) + '''' + N', ';

[Code] ...

Is the data type of the column used for partitioning. All data types are valid for use as partitioning columns, except text, ntext, image, xml, timestamp, varchar(max), nvarchar(max), varbinary(max), alias data types, or CLR user-defined data types.

In this case why isn't datetime works?

version is as follow:

Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

from [URL] .....

Table and index partitioning is supported in this edition

so I don't know why it fails!

View 1 Replies View Related

SQL Server 2012 :: Incorporate RANK Or NTILE With LEAD Function?

Jan 9, 2014

We have accounts that pay for a particular "premium" service. It's entirely possible an account paid for this service for three consecutive months in 2013, then stopped paying, then started paying again. Why I'm trying to establish is, for the FIRST period of time the accout paid for this service, for how many consecutive months did they pay? Here is my test data:

if object_id('tempdb..#SampleData') is not null
drop table #SampleData
go
if object_id('tempdb..#DateAnalysis') is not null
drop table #DateAnalysis
go

-- Create temp tables for sample data

create table #SampleData
(AccountID int,
RandomDate datetime)
create table #DateAnalysis
(RowID int identity(1,1),
AccountID int,
RandomDate datetime,
NextDate datetime,
LeadInMonths int)

-- Insert some sample data

insert into #SampleData values
(1, '1/1/13'), (1, '2/1/13'), (1, '3/1/13'), (1, '6/1/13'), (1, '10/1/13'), (1, '11/1/13'), (1, '12/1/13'),
(2, '1/1/13'), (2, '4/1/13'), (2, '5/1/13'), (2, '6/1/13'),
(3, '2/1/13'), (3, '3/1/13'), (3, '4/1/13'), (3, '9/1/13'), (3, '10/1/13'), (3, '11/1/13')

-- Use lead function to determine how many months are between

-- consecutive dates per account

; with DateInterval as
(select AccountID, RandomDate,
NextDate = lead (RandomDate, 1, NULL) over (partition by AccountID order by RandomDate)
from #SampleData)
insert into #DateAnalysis
select AccountID, RandomDate, NextDate,
datediff(mm, RandomDate, NextDate) as 'Lead'
from DateInterval

where NextDate is not null -- Last row will contain NULL for NextDate. Don't include these rows.

-- Show the results

select *,
'NTile' = NTILE(3) over (partition by AccountID order by RandomDate),
'RowNum' = row_number() over (partition by AccountID order by RandomDate)
from #DateAnalysis

Results (this is not getting me what I'm looking for):

RowIDAccountIDRandomDateNextDateLeadInMonthsNTileRowNum
11 2013-01-012013-02-01111
21 2013-02-012013-03-01112
31 2013-03-012013-06-01323
41 2013-06-012013-10-01424
51 2013-10-012013-11-01135
61 2013-11-012013-12-01136
72 2013-01-012013-04-01311
82 2013-04-012013-05-01122

[code]....

This is what I'm trying to achieve:

RowIDAccountIDRandomDateNextDateLeadInMonthsRankForThisLeadInMonths
11 2013-01-012013-02-0111
21 2013-02-012013-03-0111
31 2013-03-012013-06-0132
41 2013-06-012013-10-0143
51 2013-10-012013-11-0114
61 2013-11-012013-12-0114

[code]....

The problem comes with accounts like AccountID = 1. They paid consecutively to start, then skipped, then started paying consecutively again. When using window functions, I'm running into trouble attempting to partition by AccountID and LeadInMonths. It's putting all the LeadInMonths = 1 together and that will give me skewed results if I want to know the earliest and latest date within the FIRST consecutive range of dates where the account paid. I've tried NTILE but it expects an integer and there's no telling how many "tiles" would be in AccountID partition.

I've looked at the OVER clause and the new "ROWS BETWEEN" syntax and still cannot get the desired results.

View 5 Replies View Related

SQL Server 2012 :: Passing Parameters To Table Valued Function?

Mar 13, 2014

I am creating a function where I want to pass it parameters and then use those parameters in a select statement. When I do that it selects the variable name as a literal not a column. How do I switch that context.

Query:

ALTER FUNCTION [dbo].[ufn_Banner_Orion_Employee_Comparison_parser_v2]
(
@BANNER_COLUMN AS VARCHAR(MAX),
@ORION_COLUMN AS VARCHAR(MAX)
)
RETURNS @Banner_Orion_Employee_Comparison TABLE

[code]....

Returns:

I execute this:

select * from ufn_Banner_Orion_Employee_Comparison_parser_v2 ('a.BANNER_RANK' , 'b.[rank]')

and get:

CerecerezNULLNULLa.BANNER_RANKb.[rank]

View 7 Replies View Related

SQL Server 2012 :: Get ID From Lookup Table Based On Value Passed To Function

Dec 19, 2014

I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.

I have attached the look up table data for reference.I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color. If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.

If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.

The exact colorid of the combination must be returned on passing the values to p1 and p2.I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like

colorid p1p2p3p4p5p6p7p8
1 redred
10 redredred
20 redredred
30 redredredred
40 redredredredred
50 redredredredredred
60 redredredredredredred
70 redredredredredredredred

So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.I need a function or procedure that will accept the arguments and provide me the result based on the values.

View 2 Replies View Related

SQL Server 2012 :: Correlated Query To INNER JOIN Or Window Function

Mar 31, 2015

I'm having some performance issues with a TSQL query. It's a complex statement but the main issue is the correlated query.

How can I convert this

SELECT TOP 5
(SELECT SUM(lt2.col3)
FROM dbo.MyTable2 lt2
WHERElt2.col1 = lt.col1 AND lt2.col2 = lt.col2 AND lt2.id = lt.id ) AS Result
FROM dbo.MyTable1 t1
... to an inner join or a sql2012 window function?

By the way, I just added the TOP 5 myself while testing. It's not in the main query.

View 9 Replies View Related

SQL Server 2012 :: Cursor Function And Drop User / Logon

Aug 28, 2015

I have a temptable with a list of user IDs that I want to drop so I created a script to do a cursor and run through my drop functions. The drops work by themselves and the ver check works with them but when I wrap them in the cursor all i get is an output for each user in the results window in ssms. why it's not setting the variable and instead outputting to results?

DECLARE @ver nvarchar(128);
DECLARE @UserName nvarchar(50);
DECLARE @UserD nvarchar(80);
DECLARE @LoginD nvarchar(80);
-- Initialize the variable.
SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)

[code]...

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved