Query To ADD/SELECT Values From An SQL
Sep 16, 2004
Hello.
I need a query that will RETRIEVE a value from a database if it is present, but if the data isn't present, then the data will be INSERTed into the table.
Either way, I need the row returned at the end of the query.
I can do SELECT queries, but I don't have a clue as to how to proceed with branching statements.
For example:
User runs a query for "Canada".
Canada exists in the database, so the database returns Canada along with its ID.
Next user runs a query for "Chile".
Chile isn't in the database so a record is created and the ID (an IDENTITY field) is returned.
Does anyone know how I may accomplish this?
View 2 Replies
ADVERTISEMENT
Apr 28, 2015
How to include row values as columns in my select query. I have a table that stores comments for different sections in a web application. In the table below, I would like display each comment as a new column. I only want one row for each record_ID.
Existing table layout
table name - tblcomments
Record_ID Comment_Section_ID Comment
1 5 Test 5 comment
1 7 Test 7 comment
2 5 New comment
2 7 Old comment
3 5 Stop
3 7 Go
Desired table layout
table name - #tempComment
Record_ID Comment_Section_5 Comment_Section_7
1 Test 5 comment Test 7 comment
2 New comment old comment
3 Stop Go
Once I figure out how to get the data in the layout above, I will need to join the table with my record table.
table name - tblRecord
Record_ID Record_Type_ID Record_Status
1 23 Closed
2 56 Open
3 67 Open
4 09 Closed
5 43 In progress
I would like to be able to join the tables in the query below for the final output.
Select r.Record_ID, r.Record_Type_ID, r.Record_Status,
c.Comment_Section_5, c.Comment_Section_7
from tblRecord r
left outer join #tempComment c
on r.record_ID = c.record_ID
How I can get the data in the desired #tempComment table layout mentioned above?
View 2 Replies
View Related
Nov 6, 2007
Hi All ,
I am having a table which contain data in below form
id startvalue endvalue
1 12:00a 12:29a
2 12:30a 12:59a
3 1:00a 1:29a
4 1:00p 1:29p
5 2:00a 2:29a
Through SQL query I want to select the id column value for the row returned for the following query
select id from mytable where '1:24a' >= startvalue and '1:24a' <= endvalue
Idea is I will pass a literal value that lies between the startvalue (nvarchar column) and endvalue (nvarchar column) to fetch the id of the row returned, which should be 3 But when I run the query it returns me 2 rows for id 3 and 4.
I believe this is because I am trying to compare nvarchar columns with ">=" and "<=" operators.
Can anyone suggest how i can select the correct value or how to do this.
Thanks,
Ashish
View 2 Replies
View Related
Oct 10, 2006
Hi All,
I think what am trying to do is quite basic.
I have 3 paramaters@value1, @value2,@value3 being passed into a stored
proc and each of these parameters can be blank. If one of them is blank
and the rest of them have some valid values, then I should just exclude
the column check for the value that is blank.
For e.g if all my parameters being passed are non- empty then I would
do this
select * from tblName
where column1 like @value1 and column2 like @value2 and column3 like
@value3
else if I have one of the parameter being passed as empty, I should
ignore that parameter like
if@value1 is empty then my sql should be
select * from tblName
where column2 like @value2 and column3 like @value3
I don't want to do a dyanmic sql because of rights and security issue.
I want it through a stored procedure only.
Also, all the three columns can have null values in the table.
Please let me know what is the best possible way to do this. Thanks in
advance !.
.noscripthide
{display:none;}
.noscriptinline
{display:inline;}
.noscriptblock
{display:block;}
.scripthide
{display:none;}
.scriptinline
{display:inline;}
.scriptblock
{display:block;}
.script12hide
{display:none;}
.script12inline
{display:inline;}
.script12block
{display:block;}
.lnav
{position:absolute;}
.lnavch
{margin-left:23.0ex;}
.script13hide
{display:none;}
.script13inline
{display:inline;}
.script13block
{display:block;}
.hide
{display:none;}
.hide_ie
{;}
.hide_ie
{display:none;}
img
{border:0;}
img
{border-color:#0000a0;}
input.ck
{margin-left:-2px;}
input.bt, button.bt
{padding:0 .4em 0 .4em;width:auto;overflow:visible;}
input.bt, button.bt
{width:1px;}
.fixed_width
{font-family:fixed-width, monospace;font-size:90%;}
a:visited
{color:#551a8b;}
a:active
{color:#f00;}
.inheritcolor a
{color:inherit;}
.minmaxwie
{width:100%;}
* html .minmaxwie
{;}
.fl:visited
{color:#551a8b;}
.fl:active
{color:#f00;}
.fl:link
{color:#7777CC;}
.z
{display:none;}
.on:active
{color:#f00000;}
.don:active
{color:#f00000;}
.mbody
{margin-top:4px;}
body,td,input,textarea,select
{font-family:arial,sans-serif;}
body,td
{font-size:83%;}
input,textarea,select
{font-size:100%;}
form
{margin:0;}
.tick
{font-family:webdings;text-decoration:none !important;}
.qr
{width:100%;padding:4px;font-family:arial,sans-serif;}
.nu
{text-decoration:none;}
.gt
{border-collapse:collapse;}
.gt td
{padding:.3em 4px;border-right:1px solid #ffcc33;}
.gm td
{padding:.3em 1em .3em 0px;}
.bnk
{border:1px solid #ffcc33;}
.bnk td
{border-right-width:0px !important;}
.sel td.seltd
{padding:4px 4px 4px 4px;border:1px solid #ffcc33;border-right:none;font-weight:bold;}
p.b
{margin-bottom:1.5em;margin-top:.3em;}
.adb, .adbrnav
{border-left:1px solid #fff4c2;}
.msgdate
{color:#676767;}
.md
{color:#555555;}
.st
{margin-left:-1px;}
.nb
{white-space:nowrap;}
.np
{padding:0px;}
.p
{font-weight:bold;}
.mo
{margin:.5em 0 0 0;}
.oa
{padding:2px .5em;}
.sbox
{margin-top:1em;margin-bottom:1em;}
button a:link
{text-decoration:none;color:black;}
button a:hover
{text-decoration:none;color:black;}
.b
{font-weight:bold;}
.fontsize0
{font-size:78%;}
.fontsize1
{font-size:87%;}
.fontsize2
{font-size:96%;}
.fontsize_25
{font-size:100%;}
.fontsize3
{font-size:108%;}
.fontsize4
{font-size:120%;}
.fontsize5
{font-size:133%;}
.fontsize6
{font-size:150%;}
.fontsize7
{font-size:150%;}
.cv
{width:100%;}
.lk
{color:#0000CC;text-decoration:underline;cursor:pointer;}
.nl
{padding:5px 0 2px 5px;}
.tsh
{border-top:1px solid #ffcc33;}
.tlsh
{border-top:1px solid #ffcc33;}
.blsh
{border-bottom:1px solid #ffcc33;}
.bsh
{border-bottom:1px solid #ffcc33;}
.lsh, .tlsh, .blsh
{border-left:1px solid #ffcc33;}
.lnav
{left:9px;width:23.0ex;overflow:hidden;}
.lnavch
{;}
.lnavi
{font-size:100%;}
.lnavim
{margin-left:-2px;}
* html .lnav
{left:11px;}
.alertboxout
{margin:5px 15px 0px 10px;clear:left;}
.alertboxin
{clear:left;color:black;background-color:#fad163;font-weight:bold;text-align:center;padding:0px 15px 0px 15px;position:relative;margin:-1px 0px;}
.ctl
{padding-left:2px;}
.mb
{padding:6px 8px 0 5px;}
.exh
{margin:0 0 0 5px;background-color:#e8e8e8;}
.exh div div
{padding-top:4px;}
.thread_star
{padding:0 0 4px 2px;}
* html .thread_star
{padding:0 0 0 2px;}
.blurb_star
{padding:0 0 0 2px;}
* html .blurb_star
{padding:2px 0 0 2px;}
View 7 Replies
View Related
Aug 7, 2007
Hello all. I hope someone can offer me some help. I'm trying to construct a SQL statement that will be run on a Dataset that I have. The trick is that there are many conditions that can apply. I'll describe my situation:
I have about 1700 records in a datatable titled "AISC_Shapes_Table" with 49 columns. What I would like to do is allow the user of my VB application to 'create' a custom query (i.e. advanced search). For now, I'll just discuss two columns; The Section Label titled "AISC_MANUAL_LABEL" and the Weight column "W". The data appears in the following manner:
(AISC_Shapes_Table)
AISC_MANUAL_LABEL W
W44x300 300
W42x200 200
(and so on)
WT22x150 150
WT21x100 100
(and so on)
MT12.5x12.4 12.4
MT12x10 10
(etc.)
I have a listbox which users can select MULTIPLE "Manual Labels" or shapes. They then select a property (W for weight, in this case) and a limitation (greater than a value, less than a value, or between two values). From all this, I create a custom Query string or filter to apply to my BindingSource.Filter method. However I have to use the % wildcard to deal with exceptions. If the user only wants W shapes, I use "...LIKE 'W%'" and "...NOT LIKE 'WT%" to be sure to select ONLY W shapes and no WT's. The problems arises, however, when the user wants multiple shapes in general. If I want to select all the "AISC_MANUAL_LABEL" values with W <= 40, I can't do it. An example of a statement I tried to use to select WT% Labels and MT% labels with weight (W)<=100 is:
Code SnippetSELECT AISC_MANUAL_LABEL, W
FROM AISC_Shape_Table
WHERE (W <= 100) AND ((AISC_MANUAL_LABEL LIKE 'MT%') AND (AISC_MANUAL_LABEL LIKE 'WT%'))
It returns a NULL value to me, which i know is NOT because no such values exist. So, I further investigated and tried to use a subquery seeing if IN, ANY, or ALL would work, but to no avail. Can anyone offer up any suggestions? I know that if I can get an example of ONE of them to work, then I'll easily be able to apply it to all of my cases. Otherwise, am I just going about this the hard way or is it even possible? Please, ANY suggestions will help. Thank you in advance.
Regards,
Steve G.
View 4 Replies
View Related
Jul 27, 2015
I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
View 9 Replies
View Related
May 19, 2006
How do I:Select f1, f2, f3, from tb1 where f1=Select f1 from tb2 where f1='condition'?
View 3 Replies
View Related
Feb 8, 2008
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
View 9 Replies
View Related
Aug 19, 2015
I have a stored procedure that selects the unique Name of an item from one table.
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
How can I code a stored procedure to do this?
View 17 Replies
View Related
Jan 23, 2008
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View 7 Replies
View Related
Sep 6, 2006
Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure
declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'
SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent
'Now for each result of the above I need to run the below query
SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)
'@newpin being the result from first query
Any help appreciated
View 4 Replies
View Related
Jun 21, 2006
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
How can I do that?
Thanks.jsn
View 6 Replies
View Related
Jul 17, 2007
I'm building a textbox with search functionality towards SQL 2000.
The textbox is to include various search phrases, like "phrase1 phrase2 phrase3". Sort of like search engines where all words are considered. Do keep in mind that it's SQL2000 and not 2005. I've got all the strings I want to perform the search against in one column but various values in this column.
The only thing I've come up with is to create "WHERE columnName LIKE @phrase1 OR columnName LIKE @phrase2" where I inject % as apropiate and chop up the string, but I'm hoping this is not the solution because it doesn't seem really clean (with 10 search words) and today I'm not having a 'bad-hack' day so I want it better.
Whats is the best way of doing this?
Cheers!
/Eskil
View 3 Replies
View Related
Feb 12, 2008
I have a table wih multiple records and some has common values.
For example, new child row need to inherit some of parent's data.
Here is how I want to do:
Select [parent data1], [parent data2] from [TABEL] where id = parent
Insert INTO [TABLE] ([child id], [child data1], [child data2] ) VALUES [child id] [parent id] [parent id]...........
I was thinking about using Selected and Inserting events but not sure how.
Please help?
Thanks,
View 1 Replies
View Related
May 22, 2002
I have a table which has 4 fields, patientid,testdate,testtype,results.
I want to select the most recent testdate by a patient regardless
of the results, or the testtype. I do however need those fields for my query.
I tried the below, but I get more than 1 record if the person has had two different types of tests. For instance if patient 100 has the following 2 records I just want the most recent
patientid testdate testype results
100 01/02/2002 TBI ASYMP
100 02/02/2001 PPD 00000
select max(testdate)as testdate,testtype, other_id_number
from vw_cms_tb_lasttest
What am missing?
Thanks
group by other_id_number,testtype
order by other_id_number,testtype
View 1 Replies
View Related
Jan 26, 2004
I am using a bcp command to cretae a text file.
set @bcpcommand = 'bcp "select med + replicate('' '',10-datalength(med)),ml+ replicate('' '',20-datalength(ml)),iname + replicate('' '',10-datalength(iname)) from copy_tbl" queryout "'+ @filename + '" -U -P -c'
exec master..xp_cmdshell @bcpCommand
Some of the values in the select statement are Null values and are getting skipped in the text file .My output looks like
At A02 At1E
AtE A03 At2E
c100 c1230
I want them to allign but the third row has a null in the middle so it skips it and put the third value in the seconds place
View 2 Replies
View Related
Jun 13, 2008
I am running a select statement against a function that is giving me different values depending on how it is called. When I run it through sql server management studio, I get the proper results, the default value column has the parameters default value. When I call it through my web app, I get this in the default value column:
*** ERROR OCCURRED level 2 (this is not a default value) ***
Why would the same sql statement get different results?
Here is the call:
select * from dbo.f_GetSProcParameters('webservices_BENEFICIAL_USES_DM_SELECT')
Here are the two functions:
---------------------------------------------------
USE [si_training_db]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetSProcParameters] Script Date: 06/13/2008 09:29:21 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[f_GetSProcParameters](@StoredProcedureName VARCHAR(128) )
Returns @sProcParameters Table(ParmName VarChar(128),
DefaultValue VarChar(128),
HasDefault Bit,
IsInput Bit)
/*
* DESCRIPTION : This function returns a table listing all the parameters of a stored
*procedure and the default values of those parameters.
*
* RETURNS : table
*
*
* ORIG AUTHOR : Josh Kinder
* DATE WRITTEN : 3/14/2006
*
* REVISIONS LOG
*
*ID/Date PC# Description
*------- --- ----------------------------------------------------------
*/
As
Begin
Declare
@Count SmallInt,
@Index SmallInt,
@CurParm VarChar(128),
@DefaultVal VarChar(128),
@IsInput BIT
/*----------------------------------------------------------------------------------------------------------------------------------*/
--EDIT CHECK -sProc only
/*----------------------------------------------------------------------------------------------------------------------------------*/
-- Check that the proc name is valid
If OBJECT_ID(@StoredProcedureName, 'P') Is Null
Begin
Goto ScriptErr
End
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
Declare @ParmTable Table (Id SmallInt Identity Primary Key Clustered, ParmName VarChar(128))
Insert Into @ParmTable
Select
a.Name
From
SysColumns a
Inner Join SysObjects b On b.Id = a.Id
Where
b.Name = @StoredProcedureName
Select
@Count = Count(Id),
@Index = 1
From
@ParmTable
While (@Index<=@Count)
Begin
Select @CurParm = ParmName From @ParmTable Where Id = @Index
Set @DefaultVal = dbo.f_GetsProcParamDefaultValue(@StoredProcedureName,
@CurParm)
SELECT@IsInput = CASE WHEN params.is_output = 1 THEN 0 ELSE 1 END
FROMsys.procedures AS procs
INNER JOINsys.all_parameters AS params ON params.object_id = procs.object_id
LEFT JOINsys.types AS types ON types.system_type_id = params.system_type_id
AND types.user_type_id = params.user_type_id
WHEREprocs.is_ms_shipped = 0
AND params.name = @CurParm
AND procs.name = @StoredProcedureName
Insert Into @sProcParameters
(
ParmName,
DefaultValue,
HasDefault,
IsInput
)
Values
(
@CurParm,
@DefaultVal,
Case When @DefaultVal = 'NoDefaultExists' Then 0 Else 1 End,
@IsInput
)
Set @Index = @Index + 1
End
ScriptErr:
Return
End
---------------------------------------------------
USE [si_training_db]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetsProcParamDefaultValue] Script Date: 06/13/2008 09:30:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[f_GetsProcParamDefaultValue]
(
@StoredProcedureName VarChar(128),
@ParamName VarChar(128)
)
ReturnsVarChar(128)
/*
* DESCRIPTION : This function returns a table listing all the parameters of a stored
*procedure and the default values of those parameters.
*
The following copyright info is for the parsing algorithm to get the default value.
I obtained the code from SQL Farms Solutions and their only stipulation for use is
that the copyright info remain with the code. Although I customized it for us, it is
still their algorithm.
==================================================================================
Copyright © SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code may be used at no charge as long as this copyright notice is not removed.
==================================================================================
* RETURNS : table
*
*
* ORIG AUTHOR : Josh Kinder
* DATE WRITTEN : 3/14/2006
*
* REVISIONS LOG
*
*ID/Date PC# Description
*------- --- ----------------------------------------------------------
*/
As
Begin
Declare
@minRow Int,
@maxRow Int,
@tmpInt Int,
@tmpText VarChar(4000),
@tmpCharPos1 Int,
@tmpCharPos2 Int,
@tmpCharPos3 Int,
@ParameterDefault VarChar(128),
@sProcTextVarChar(4000),
@DelimiterChar(1),
@Count SmallInt,
@Index SmallInt,
@CurTextVarChar(4000)
Select
@minRow = Null,
@maxRow = Null,
@Delimiter = char(13)
Declare @ProcText Table (Id Int Identity(1, 1) Primary Key Clustered,
ProcText VarChar(4000))
Insert Into @ProcText
Select
a.Text
From
SysComments a
Inner Join SysObjects b On b.Id = a.Id
Where
b.Name = @StoredProcedureName
Select
@Count = Count(Id),
@Index = 1
From
@ProcText
While (@Index<=@Count)
Begin
Select @CurText = Coalesce(@CurText,'') + ProcText From @ProcText Where Id = @Index
Set @Index = @Index + 1
End
/*----------------------------------------------------------------------------------------------------------------------------------*/
--EDIT CHECKS
/*----------------------------------------------------------------------------------------------------------------------------------*/
-- Check that the parameter name is valid for the proc
If Not Exists(
Select
1
From
INFORMATION_SCHEMA.PARAMETERS
Where
SPECIFIC_NAME = @StoredProcedureName
And PARAMETER_NAME = @ParamName
)
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 1 (this is not a default value) ***'
Goto ScriptErr
End
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
--Get sProc into a workable temporary table
/*----------------------------------------------------------------------------------------------------------------------------------*/
Declare @ProcContent Table (Idx Int Identity(1, 1) Primary Key Clustered,
ProcText VarChar(4000))
Insert Into @ProcContent
Select Value From dbo.f_Split(@CurText, @Delimiter, 1, 0)
-- Make sure that some rows were returned successfully
If @@ROWCOUNT = 0
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 2 (this is not a default value) ***'
Goto ScriptErr
End
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
--Get location of parm and get ready to parse
/*----------------------------------------------------------------------------------------------------------------------------------*/
-- Get the first line where the parameter is referenced in the proc code.
-- (the LIKE here is a little complex since it is possible that multiple parameters
-- will start with the same string. Most cases of interest are covered by the
-- conditions listed below).
Select
@minRow = Min(Idx)
From
@ProcContent
Where
ProcText Like '%' + @ParamName + ' %'
Or ProcText Like '%' + @ParamName + Char(9) + '%'
Or ProcText Like '%' + @ParamName + Char(10) + '%'
Or ProcText Like '%' + @ParamName + Char(13) + '%'
Or ProcText Like '%' + @ParamName + '=%'
Or ProcText Like '%' + @ParamName + '%=%'
Or ProcText Like '%' + @ParamName + ',%'
-- Check that the parameter is referenced in the code
If @minRow Is Null
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 3 (this is not a default value) ***'
Goto ScriptErr
End
-- Get the proc line where the word 'AS' is declared. 'AS' is required
-- upon proc creation to complete the variable declaration.
-- Note: This cover most cases of interest. There could be scenarios where
-- additional condition should be applied.
Select
@maxRow = Min(Idx)
From
@ProcContent
Where
ProcText Like '% AS'
Or ProcText Like '% AS '
Or ProcText Like '% AS' + Char(9)
Or ProcText Like '% AS' + Char(10)
Or ProcText Like '% AS' + Char(13)
Or ProcText Like 'AS %'
Or Upper(RTrim(LTrim(ProcText))) = Char(10) + 'AS'
Or Upper(RTrim(LTrim(ProcText))) = 'AS'
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(13) + Char(10)
Or Upper(RTrim(LTrim(ProcText))) = 'AS' + Char(10) + Char(13)
-- Check that the 'AS' string was found successfully
If @maxRow Is Null
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 4 (this is not a default value) ***'
Goto ScriptErr
End
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
--Parse and get the default value
/*----------------------------------------------------------------------------------------------------------------------------------*/
-- Get the first proc line of code where the parameter is referenced, for string processing,
-- and append to it all proc rows until the 'AS' string
Select
@tmpText = LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' '
From
@ProcContent
Where
Idx = @minRow
While @minRow < @maxRow
Begin
Set @minRow = @minRow + 1
Select
@tmpText = @tmpText + ' ' + LTrim(RTrim(Replace(Replace(ProcText, Char(10), ''), Char(13), ''))) + ' '
From
@ProcContent
Where
Idx = @minRow
End
-- Find the position of the parameter name. Delete all text before that position.
Set @tmpInt = Null
Set @tmpInt = PatIndex('%' + @ParamName + '%', @tmpText) - 1
Set @tmpText = Right(@tmpText, Len(@tmpText) - @tmpInt)
-- At this point we are nearly done:
-- We check whether the character '=' comes before the 'AS' or ',' string
-- If not- the parameter has no default value.
-- If so, we continue to find the value of the default parameter
Set @tmpCharPos1 = PatIndex('%=%', @tmpText)
Set @tmpCharPos2 = PatIndex('%,%', @tmpText)
Set @tmpCharPos3 = PatIndex('% AS %', @tmpText)
If @tmpCharPos1 <= 0
Or (@tmpCharPos1 > @tmpCharPos2 And @tmpCharPos2 > 0)
Or (@tmpCharPos1 > @tmpCharPos3 AND @tmpCharPos3 > 0)
Begin
-- The column does not have a default
Set @ParameterDefault = 'NoDefaultExists'
End
Else
Begin
-- Column has a default and it is left to find it.
-- First chop the string until the '=' character
Set @tmpInt = NULL
Set @tmpInt = PatIndex('%=%', @tmpText) - 1
Set @tmpText = LTrim(Right(@tmpText, Len(@tmpText) - @tmpInt))
-- Now, we p*** the remaining string until we get a ',' or a ' ' character
Set @tmpCharPos1 = NULL
Set @tmpCharPos2 = NULL
Set @tmpCharPos1 = PatIndex('%,%', @tmpText)
Set @tmpCharPos2 = PatIndex('% %', @tmpText)
Set @tmpInt = NULL
If @tmpCharPos2 > @tmpCharPos1 AND @tmpCharPos1 > 0
Set @tmpInt = @tmpCharPos1 - 1
Else
Set @tmpInt = @tmpCharPos2 - 1
If @tmpInt <= 0
Begin
Set @ParameterDefault = '*** ERROR OCCURRED level 5 (this is not a default value) ***'
Goto ScriptErr
End
Set @ParameterDefault = SubString(@tmpText, 1, @tmpInt)
-- If the parameter default is a string, then we will have an '' at each side of it.
-- These last lines of code will get rid of the ''.
If Len(@ParameterDefault) >= 1
If Right(@ParameterDefault, 1) = ''''
Set @ParameterDefault = Left(@ParameterDefault, Len(@ParameterDefault) - 1)
If Len(@ParameterDefault) >= 1
If Left(@ParameterDefault, 1) = ''''
Set @ParameterDefault = Right(@ParameterDefault, Len(@ParameterDefault) - 1)
End
/*----------------------------------------------------------------------------------------------------------------------------------*/
/*----------------------------------------------------------------------------------------------------------------------------------*/
Goto ScriptExit
ScriptErr:
ScriptExit:
Return @ParameterDefault
End
View 1 Replies
View Related
Jun 25, 2014
I have a problem when selecting distinct values from a table. I want to select those distinct numbers with a code that I want. To make it more clear i`ll show it on an example.I have the following table:
tel_no | code | id_res
+-----------+--------------+-----------
0742062141 | a-not answer | 1
0742062141 | a-not answer | 2
0742062141 | c-answer | 3
0754231456 | a-not answer | 4
0754231456 | a-not answer | 5
When I use the following query it returns the distinct number but with the first code it finds:
select a.* from test_funnel as a
inner join
( select distinct tel_no,MIN(id_res) as id_res
from test_funnel
[code]....
View 3 Replies
View Related
Sep 15, 2006
I am new to SQL and I am trouble-shooting a problem with a home-grown app someone else wrote using PERL. It has a web interface with names of boards. I found the program where i need to add the board names into and did that. The new board names show up in the drop-down list in the Web page for the app. Alerts are sent to the new board names and show up on the new boards. Users are granted access to the new boards and they can clear items off the new boards. Yet when i try to use a report function for the new boards i added, nothing is returned. I even ran a simple SQl select statement specifying the new board names and nothing is returned. The older board names, some of which i added myself, return values. I don't know what is going on. Any help is appreciated.
View 3 Replies
View Related
Oct 4, 2006
hi, i have 5 columns in my table
name age phone sex salary
dani 21 1233 m
see this output, there salary is null.
actually i want to use * (ie)
select * from emp, if i use this the following output has to come
name age phone sex
dani 21 1233 m
becos there is no value in salary column.
so please tall me how to do this,please give me query to do this.
View 5 Replies
View Related
Jul 27, 2006
Hello,I need to select the minimum between the result of a function and anumber, but i can't find a smart way. By now I'm doing like thefollowing, but I think is very expensive because it evaluates thefunction twice:select case when (myfunction())<100 then (myfunction()) else 100 endAny idea is appreciated.Thank youRegards--elimina carraro per rispondere via email
View 4 Replies
View Related
Jul 20, 2005
I am new to sql query and have a question.I would like to display row column values as column names.questions table consisted ofquestion_id, group_id, questionresponse table consisted ofresponse_id, group_id, question_id, answerI would like to display the answer responses under the columns ofquestions : such asresponse_id, question(1), question(2), question(3)...It will be greatly appreciated if someone help me to get this down.Jason
View 2 Replies
View Related
Jul 9, 2002
When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks
View 1 Replies
View Related
May 20, 2007
Hi,
I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2.
The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all.
public string str;protected void Button3_Click(object sender, EventArgs e)
{ArrayList itemsSelected = new ArrayList(); string sep = ",";
//string str;for (int i = 0; i < ListBox2.Items.Count; i++)
{if (ListBox2.Items[i].Selected)
{
itemsSelected.Add(ListBox2.Items[i].Value);
}
int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items.
str = ListBox2.Items[i].Value + sep;
Response.Write(str);
}
SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False");
String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable();
Adptr.Fill(Dt);
//return Dt;
GridView1.DataBind();
SqlCon.Close();
}
I did some changes and the new error message is
Incorrect syntax near the keyword 'from'.
Thank you
View 6 Replies
View Related
May 20, 2007
Hi, I have something similar to the following: CREATE PROCEDURE dbo.MySproc @columnVal int = nullASBEGIN SELECT * FROM MyTable WHERE MyTable.column = @columnVal END If columnVal is not passed into the stored proc i want it to just select everything from 'MyTable' (without the WHERE clause)....how can I do this in as few lines of code possible?thanks
View 3 Replies
View Related
Sep 4, 2007
Hi.
I have an sql table which contains a number and a name. I would like to create a select statement that will display these two fields in the format :
"number | name", but if there is a null value in the number it will display only the name and vice versa.
How can I do it ?
Any help is appreciated.
View 1 Replies
View Related
Sep 19, 2007
Hi,
I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement.
I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'."
The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int)
AS
BEGIN
WITH SearchResults AS(
UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
)
SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END
Thanks in advance for any help.
View 3 Replies
View Related
Jun 3, 2008
Hello there i try to retrieve some data from my table but i cannot get it to get me the ItemID from the table
here is the connection string it jumps right at the start of the loop strconnection = New SqlConnection("Data source = REPORT-SRVR; Initial Catalog=Cincinnati_Tan_Company;Persist Security Info=True;User ID=basic_user;Password=ctc_user;")
Dim cmdstring As String = "select ItemID, Price FROM Office_Items WHERE Description ='" & item(INTINDEX) & "'" '
strconnection.Open()CMD = New SqlCommand(cmdstring, strconnection)
mYReader = CMD.ExecuteReader()
If mYReader.Read = True ThenWhile mYReader.Read
ItemID = mYReader.Item("ItemID")Session("ItemID") = mYReader.Item("ItemID")
Session.Add("ItemID", ItemID)price = mYReader.Item("price")
Session("price") = mYReader.Item("price")Session.Add("price", price)
End While
End If
mYReader.Close()
View 3 Replies
View Related
Jan 22, 2006
What is the best way to build a SQL query to extract data from a table in this manner:
COLA COLB COLC DATE
01 01 01 04/15/1988
01 01 01 11/23/1997
01 01 01 02/02/2005
01 02 01 6/14/1990
01 02 01 10/30/1999
01 02 02 4/24/2001
How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?)
Thanks!!!
View 1 Replies
View Related
Jul 12, 2012
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB
View 4 Replies
View Related
May 18, 2004
I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.
I created a test in MS Access and it loooks like this:
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country
This works great in Access but not in SQL Server. In SQL Server 2 = @PatientTripID
ANY SUGGESTIONS ON HOW TO HANDLE THIS?
View 7 Replies
View Related
May 6, 2014
I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.
DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL
[code]....
So this would be the resultset:
1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27
View 3 Replies
View Related
