SQL Server 2014 :: Like Statement With Parameters

Sep 30, 2015

I have a table :

Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))

and I have a sp for search data in "Person" table:

CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL

[Code] ....

And there exists 500 record in Person.

I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'ام',
@Last = NULL,
@Address= NULL

SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?

View 9 Replies


ADVERTISEMENT

SQL Server 2014 :: How To Get A Pivot Statement Without Aggregate

Jun 11, 2014

I want to pivot a table something like this .I pivoted it successfully but the results are not correct.

Here is the example :

install-Name Fiscal year Question Answer
Washington 2010 what is the reason for install? tttttt ggg yttt o
washington 2010 reason id 12345
washington 2010 install start date 10/10/2010
washington 2010 install end date 10/12/2010
washington 2010 install status successfull

[code]....

I want the above data to get pivoted like this

Install-name | Fiscal year |what is the reason for install? | reason id | install start date | install end date |
install status |Do you feel the install is incomplete? | Is the expiration of 90 days exceeded? |

washington | 2010 | tttttt ggg yttt o | 12345 | 10/10/2010 | 10/12/2010 |
successful | | |
washington | 2010 | trtbnbthwgt hrgthjrt | - | 12/3/2010 | 12/8/2010 |
| | |
washington | 2011 | sbjeh dhebwdh dbjw | 345 | 10/10/2010 | 10/12/2010 |
successful | No | Yes ,but b b b b |

View 2 Replies View Related

SQL Server 2014 :: Union All Statement Hangs Engine?

Sep 9, 2014

I have TSQL query using UNION ALL. First subset returns 92 rows, second subset return 0 (ZERO) rows. Now if I execute them separately they execute subsecond. If I put UNION ALL between them then it hangs forever exactly on 88s row.

I understand I need to provide execution plans, actual queries etc but before going into all this details, why on earth even in theory UNION ALL with empty result will result in a hang of the first subset?

Based on estimated execution plan 2 subqueries are performed in parallel and then CONCATENTION is being used which shall result with just first subset immediately displayed in result.

Estimated execution plan attached, I can not get actual execution plan because query never completes.

View 9 Replies View Related

SQL Server 2014 :: Finding NULL In CASE Statement

Sep 15, 2015

The below code returns a NULL for all the Differential backups. I am trying to return the word Differential when there is a NULL because I can tell from when the NULLs occur that those are my differential backups. How can I get the result I want?

SELECT
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,

CASE
WHEN msdb..backupset.type = 'D' THEN 'Database'

[Code] .....

View 9 Replies View Related

SQL Server 2014 :: Table Locking While Insert Statement

Sep 18, 2015

I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.

View 5 Replies View Related

SQL Server 2014 :: Adding One More Column For SubTotal In Select Statement?

Feb 13, 2014

We have a table with 2 columns 'OrderNo' and 'Amount' as below

ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000

as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below

ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500

View 7 Replies View Related

SQL Server 2014 :: How To Call Dynamic Query Stored Procedure In Select Statement

Jul 23, 2014

I have created a stored procedure with dynamic query and using sp_executesql . stored procedure is work fine.

Now i want to call stored procedure in select statement because stored procedure return a single value.

I search on google and i find openrowset but this generate a meta data error

So how i can resolve it ???

View 7 Replies View Related

SQL Server 2014 :: Select Statement Returning Too Many Rows For Backup Start Date

Jun 3, 2015

Here's my statement below. What I'm trying to get is joining the name column in master.sys.databases with a sub query for the database name, file location and backup start date from the MSDB database. The reason for this, if a new database has never been backed up, It should be returning as a NULL value, which is my goal. However, I'm getting multiple results for the backups.

select CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,a.name,File_Location=b.physical_device_name,backup_start_date=max(backup_start_date)
from master.sys.databases a
left join(select c.database_name,backup_start_date=max(backup_start_date),b.physical_device_name
from msdb.dbo.backupmediafamily b join msdb.dbo.backupset c on c.media_set_id=c.backup_set_id
where c.type='D'

[Code] .....

View 8 Replies View Related

Parameters With LIKE Statement

Aug 24, 2005

I have a sql that I want to execute with LIKE and parameters:I tried several options outlined that I found at http://aspnet101.com/aspnet101/tutorials.aspx?id=10%20but they all seem to return 0 records.  When I try and execute my statement in Enterprise manager, it works fine.Code Snippet:...Dim sql as stringsql = "SELECT * FROM tblName WHERE First LIKE '%' + @fname + '%' AND Last LIKE '%' + @lname + '%'"Dim param(1) as sqlParametersqlParams(0) = New SqlParameter("@fname", SqlDbType.VarChar, 50)sqlParams(0).Value = Trim(fname.text)sqlParams(1) = New SqlParameter("@lname", SqlDbType.VarChar, 50)sqlParams(1).Value = Trim(lname.text)...Can anyone tell me if there is anything wrong with my code above?

View 8 Replies View Related

WITH Statement And OLE DB Parameters

May 27, 2008

Hi,

I have an issue with an OLE DB command and parameters. The statement I want to run starts with a WITH statement - when I take out the parameter it runs fine. When I take out the WITH statement and leave the parameter in it runs fine. When I run the statement in SSMS it runs fine.

With both the WITH statement and the parameter in it gives the error 'Syntax error, permission violation, or other nonspecific error'.

Has anyone seen this before?

View 2 Replies View Related

Passing Parameters Using IN Statement

Dec 7, 2006

The SQL for a dataset in ASP.NET 2.0 is as follows....
SELECT DISTINCT StudentData.StudentDataKeyFROM         Student2Roster INNER JOIN                      StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecIDWHERE     (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs)
ClassRosterRecIDs are giuds
At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime.
At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier.
If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'?
Thanks

View 2 Replies View Related

Extract Parameters From SQL Statement

Jul 20, 2005

Hei,I'm currently trying to write a program in C# that will allow users toparametrize their queries.For instance, I have a query like this:SELECT * FROM Customers Where Region = @Region AND Gender > @GenderHow can I extract the Parameters names without using Stringmanipulation (which is not perfect since sql statements can alsocontain '@' in LIKE clauses for example)I tried this wayDim comm As New OleDbCommand(SqlStatement, myCon)comm.Prepare()MsgBox(comm.Parameters.Count.ToString())comm.Dispose()myCon.Close()but this always return me 0 (zero)Do you have any idea on how to solve this generic problem?RegarsPhilippe Graca

View 3 Replies View Related

Passing Parameters To DDL Statement?

Jul 12, 2007

Hi all of you,



I'd like to know how could I add parameters to CREATE TABLE from a Execute Sql Task.



Is it possible?



TIA

View 3 Replies View Related

Parameters With A Union Statement

Jul 3, 2007

I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.



What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.



But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"



Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?



Thanks

View 2 Replies View Related

Using Passed Parameters In A Select Statement

Aug 21, 2007

hello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!!
 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim pageID As StringpageID = Request.QueryString("ID")
TextBox13.Text = pageID 'Test to make sure the value was stored
SqlDataSource1.SelectParameters.Add("@pageID", pageID)
End Sub
.... 
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource>
 
The error that i am getting, regardless of what i put inside the ' ' is as follows:
"Conversion failed when converting the varchar value '@pageID' to data type int."
 
anyone have any suggestions ?
 

View 2 Replies View Related

Delete Statement With Parameters Not Working

Jun 13, 2008

When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok?  // Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;

// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();

// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);

// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)";
Response.Write(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close(); 
 

View 9 Replies View Related

Insert From Parameters And Select Statement

May 30, 2006

Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo

View 1 Replies View Related

ADODB 2.8 - SQL Insert Statement - Using Parameters

Nov 20, 2007

Hi there,
I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID.
But instead of it it returns error:

Run-time error '-2147217900 (80040e14)
Must declare the scalar variable "@ii_file"

Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
"?
I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side.
Thanks!

Darek


Public Sub save_import()
Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long

Set cmd = New ADODB.Command
With cmd
.ActiveConnection = ado_conn 'an existing connection
.CommandType = adCmdText
.CommandText = "insert into import_main (ii_file, id_file, oi_file, od_file, folder, import_desc, id_client,basis_of_study, id_is, project_leader, urisk_model_basis, as_at_date, extent_benchamark) " & _
"values (@ii_file, @id_file, @od_file, @od_file, @folder, @import_desc, @id_client, @basis_of_study, @id_is, @project_leader, @urisk_model_basis, @as_at_date, @extent_benchmark) " & _
"select @id_im = @@identity"
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)

.Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
.Parameters.Append .CreateParameter("@id_file", adVarChar, adParamInput, 255, Me.cbo_id)
.Parameters.Append .CreateParameter("@oi_file", adVarChar, adParamInput, 255, Me.cbo_oi)
.Parameters.Append .CreateParameter("@od_file", adVarChar, adParamInput, 255, Me.cbo_od)
.Parameters.Append .CreateParameter("@folder", adVarChar, adParamInput, 1000, Me.txt_folder)
.Parameters.Append .CreateParameter("@import_desc", adVarChar, adParamInput, 255, Me.txt_import_desc)
.Parameters.Append .CreateParameter("@id_client", adBigInt, adParamInput, Me.cbo_client)
.Parameters.Append .CreateParameter("@basis_of_study", adVarChar, adParamInput, 255, Me.txt_basis_of_study)
.Parameters.Append .CreateParameter("@id_is", adSmallInt, adParamInput, Me.cbo_status)
.Parameters.Append .CreateParameter("@project_leader", adVarChar, adParamInput, 255, Me.txt_project_leader)
.Parameters.Append .CreateParameter("@urisk_model_basis", adVarChar, adParamInput, 1000, Me.txt_urisk_model)
.Parameters.Append .CreateParameter("@as_at_date", adDate, adParamInput, CDate(Me.txt_as_at_date))
.Parameters.Append .CreateParameter("@extent_benchmark", adVarChar, adParamInput, 1000, Me.txt_extent_benchmark)
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)

.Execute rec_affected, , adExecuteNoRecords
If rec_affected > 0 Then
Me.cbo_import = .Parameters.Item("@id_im")
End If

End With


End Sub

View 3 Replies View Related

Errors Using Multiple Parameters In A SQL Statement

Feb 23, 2007

In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.

Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.

The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.

Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?

Thanks,

Mike

View 15 Replies View Related

Controlling Fields In A Select Statement By Use Of Parameters

Sep 29, 2005

Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst

View 2 Replies View Related

SQLDataSource - How To Retreive The SQL Select Statement With The Values Of The Parameters

May 1, 2007

Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
 
Thank you

View 4 Replies View Related

Can You Have Multiple Output Parameters? Difference Between A Select Statement?

Mar 9, 2004

I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?

Do I need just a select statement to do this? What is the difference between the output and select statements?

Thanks in advance.

View 1 Replies View Related

Reporting Services :: SSRS Using Case Statement With Parameters - Can It Be Done?

Jul 13, 2015

I need to run a different select statement based on the value of a parameter. Visual studio is not liking how I am formatting it.  

SELECT mc.CtrlID,  mc.PrtfID, mc.PrtfTyp, mc.City, mc.STATE, mc.U_O_AND_M, mc.CHBBal,  mc.IRG_Number, mc.PrtfName, mc.LnID,  mc.UPB, mc.UPB_Master_Loan, aa.Prtf_FNMA_FHLMC_Other , aa.ReportingSummar
FROM             vw_XXX_GIAC mc
INNER JOIN vw_XXX_InvestorMatrix aa on mc.CtrlID = aa.CtrlID
WHERE
case when
@PortfolioID=1 THEN aa.Prtf_FNMA_FHLMC_Other ='FNMA'
@PortfolioID=2 THEN aa.Prtf_FNMA_FHLMC_Other ='FHLMC'
@PortfolioID=3 THEN aa.Reporting Summar ='FLOW'
END

View 2 Replies View Related

Error Retrieving A Record Based On 3 Parameters. Need Help With Forming My SQL Statement

May 18, 2008

I am having trouble returning the correct record with my stored procedure.
my problem is that i don't know how to structure the sql statement to do the following:
given a set of records that have the same loankey, i need to
1. find the record that has most recent date (lockExprDt)
2. for all records with that date, find the highest Lock Number (LockNo)
3. for the all the records with that date and that LockNo, find the highest extension number (Ext) 
currently my sql statement returns a record that has the most recent date. i don't now how to write the sql to further define my query to return the record that has the most recent date with highest lock number, and finally the highest extension number.
any suggestions as to what i am doing wrong. below is my slq statement. please note that i need to add the sql that will query for the max LockNo, and max Ext. Any help is greatly appreciated. thx!
select a.loankey, a.lockrate, a.investor, a.price, a.ext, a.cost, a.lockno, a.lockstatus , CASE WHEN CONVERT(CHAR(8),a.lockdate,10)='12:00:00 AM'  THEN NULL ELSE CONVERT(CHAR(8),a.lockdate,10)   END as 'LockDate' , CASE WHEN CONVERT(CHAR(8),b.lockExprDt,10)='12:00:00 AM'  THEN NULL ELSE CONVERT(CHAR(8),b.lockExprDt,10) END as 'LockExprDt'  , Case WHEN CONVERT(CHAR(8),b.lockExprDt,10)>= CONVERT(CHAR(8),GETDATE(),10) THEN datediff(day, CONVERT(CHAR(8),GETDATE(),10), CONVERT(CHAR(8),b.lockExprDt,10)) ELSE NULL END as 'Days' 
from cfcdb..locktable ainner join (select loankey, max(lockExprDt) as lockExprDtfrom cfcdb..locktablegroup by loankey) bON a.loankey = b.loankey AND a.lockExprDt = b.lockExprDt
where a.loankey = @LoanKey

View 6 Replies View Related

Can A Column Be Derived Using Substring But The Parameters Are A Result Of A Select Statement?

Apr 6, 2007

I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.









Table 1

Org

aaa:aaa:aa

aaa:aaa:ab

aaa:aab:aa








Table 2














delim

Seg1Start

Seg1Len

Seg2Start

Seg2Len

Seg3Start

Seg3Len


:

1

3

5

3

9

2



My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.



Any ideas? Can this be done in SSIS?



I'd really appreciate any insight that anyone might have.



Regards,

Bill

View 23 Replies View Related

Unknown Ammount Of Parameters, Parameterized Statement/split Strings In Transact Sql

Oct 26, 2007

Hello,
Is there any way to run a parameterized statement against SQL when you have an unknown ammount of inputs.
Instead of doing the following:
FOR i as integer = 0 to topValuestrWhere &= " OR myColName='"& myArr(i) &"'"
NEXT
wash up the string and submit this to SQL, I want to do it with parameters. I'm calling a sproc in SQL and I would prefer just to send in a string like "1,4,42',45" in a parameter split that in SQL and run my query.The query would be something like "...WHERE myColname=1 OR myColname=2 OR myColName=3" I only want to declare one parameter and not use string concatenation as described above.It doesn't matter if there's a split function in SQL or not, as long as it solves my problem in an efficient manner. Cheers!/Eskil 

View 3 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Use Convert Date Statement In CmdInsert.Parameters.Add(Date,SqlDbType.DateTime).Value = Date

Sep 21, 2006

HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance

View 3 Replies View Related

SQL Server Admin 2014 :: Change IP Address Of Server Which Is Locally Hosted And Is Not On Cluster?

Sep 3, 2014

I am asking about a virtual IP for SQL Server, is there a way we can assign a different IP to SQL Server other than the server's(host) IP address? like the same what we do in a clustered env.

View 3 Replies View Related

SQL Server Admin 2014 :: Unable To Access Physical Server To Backup Transaction Log

Dec 5, 2014

I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.

Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."

I don't understand how backing up the transaction log could break the "transaction chain"?

View 9 Replies View Related

SQL Server Admin 2014 :: No Objects Listed For Server Performance Condition Alert

Jan 25, 2015

We are trying to create some alerts in our SQL Server 2014 BI edition.Issue is that, after I chose "Type" as "SQL Server performance condition alert" nothing is listed in the "Object" list box.SQL Server event alerts are working. Issue is only with "SQL Server performance condition alert".

View 3 Replies View Related

SQL Server Admin 2014 :: Failed To Prepare Storage For Testing On Node Server Name

Feb 23, 2015

I Run All checks for Validation cluster.I get Error On Disk Lists And Validation failed.With This error : Failed to prepare storage for testing on node "server name" The security account manager (SAM) or local security authority (LSA) server was in the wrong state to perform the security operation.

View 2 Replies View Related







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