Dynamic SQL Delete Query Problem
Aug 6, 2006
Hi all--Given a query that returns ~557K rows on SQL Server 2005 SP1:
SELECT distinct(a.[Import_Date]),b.[Import_Date],
a.[OS_USERNAME],b.[OS_USERNAME],a.[USERNAME],b.[USERNAME],
a.[TIMESTAMP],b.[TIMESTAMP]
FROM [DBA_AUDIT_SESSION] a, [DBA_AUDIT_SESSION] b
where a.[OS_USERNAME]=b.[OS_USERNAME]
and a.[USERNAME]=b.[USERNAME]
and a.[TIMESTAMP]=b.[TIMESTAMP]
and a.[Import_Date]<b.[Import_Date]
On a table that has the following definition:
TABLE [dbo].[DBA_AUDIT_SESSION](
[Import_Date] [datetime] NULL,
[INSTANCE_NAME] [varchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HOST_NAME] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OS_USERNAME] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERNAME] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[USERHOST] [varchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TERMINAL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TIMESTAMP] [datetime] NULL,
[ACTION_NAME] [varchar](27) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LOGOFF_TIME] [datetime] NULL,
[LOGOFF_LREAD] [float] NULL,
[LOGOFF_PREAD] [float] NULL,
[LOGOFF_LWRITE] [float] NULL,
[LOGOFF_DLOCK] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSIONID] [float] NULL,
[RETURNCODE] [float] NULL,
[CLIENT_ID] [varchar](64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SESSION_CPU] [float] NULL
I would like to delete duplicate data from this table and keep only the data with the latest Import_Date. I came up with the following delete statement:
delete FROM [DBA_AUDIT_SESSION]
FROM [DBA_AUDIT_SESSION] a,
inner join [DBA_AUDIT_SESSION] b
on a.[TIMESTAMP]=b.[TIMESTAMP]
where a.[Import_Date]<b.[Import_Date];
The command parses successfully, but I get the following runtime error:
'Table DBA_AUDIT_SESSION is ambiguous.'
Does anyone have suggestions on how to fix this delete statement?
View 3 Replies
ADVERTISEMENT
Jun 1, 2007
I have the following sp:@TABLE_NAME varchar(255),
@CONTROL_ID int
AS
DECLARE @sql varchar(4000)
SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = 5'SELECT @sql = 'DELETE FROM [' + @TABLE_NAME + '] WHERE CONTROL_ID = ' + @CONTROL_ID + ''
EXEC (@sql)
When I use the the first SELECT line, it works great.When I use the second dynamic select, SQL raises an error:
Syntax error converting the varchar value 'DELETE FROM [TABLE_SETTINGS] WHERE CONTROL_ID = ' to a column of data type int.What is wrong?
View 2 Replies
View Related
Nov 26, 2007
this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
View 1 Replies
View Related
Feb 16, 2008
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View 2 Replies
View Related
Mar 13, 2014
I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
del $(ESCAPE_SQUOTE(SQLDIR)) + "LogTestTrace.trc"
View 4 Replies
View Related
Mar 30, 2007
I need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
Thanks
View 4 Replies
View Related
Jun 20, 2006
Hi all. I'm currently encountering a problem in attempting to find a solution for a dynamic or on the fly query. I understand how you can make a static query and return it as a dataset; for example, say you have a field where a user enters a name on the front end and the db returns the results:
Dim queryString As String = "SELECT [Table].* FROM [Table] WHERE ("& _ "[Table].[Name] = @Name)"
But what if I have multiple items I would like query based upon what the user picks. For example, say you have five fields: Name, ID Number, Date, Address, and State. Say the user wants to pick all data with a date between Jan. 06 to April 06, with the name of Tom, and in the state of CA. But then next time, the user only wants all data with the name of Susan. So the query is always changing and I am not sure exactly how to go about it. I guess I sorta want similiar functionality as that of the Custom Auto Filter in Excel. I've been reading a couple of the forums and I think people are using a string to pass to query the database. But I am still vague on how to approach this. Any help would be greatly appreciated!
View 5 Replies
View Related
Sep 14, 2007
I am having trouble wrapping my head around some dynamic queries. I have x number of queries stored in a table. Each row in this table has a From, Join, and Where column. So, for x=3, I can run the query from each row so that I may have
Q1: (1, 2, 4, 5, 7, 8)
Q2: (1, 3, 5, 7, 9)
Q3: (2, 4, 6, 8, 10)
I need to use these queries to generate a shared table: |-------------------|
| ID | Q1 | Q2 | Q3 |
| 1 | 1 | 1 | 0 |
| 2 | 1 | 0 | 1 |
| 3 | 0 | 1 | 0 |
| 4 | 1 | 0 | 1 |
| 5 | 1 | 1 | 0 |
| 6 | 0 | 0 | 1 |
| 7 | 1 | 1 | 0 |
| 8 | 1 | 0 | 1 |
| 9 | 0 | 1 | 0 |
| 10 | 0 | 0 | 1 |
|-------------------| I'm not sure the best way to do this. I think that doing it on the asp.net side will be easier than in t-sql, although I am exploring both possibilities. Any suggestions?
View 13 Replies
View Related
Jun 9, 2008
Hi..
I want to change the SQL Query Dynamically ....
i have attached the Source code.....1st Page: <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ConsIndex.aspx.cs" Inherits="Cons_ConsIndex" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="sqldatasource1" Width="80%" runat="server"> <ItemTemplate> <table align="left"> <tr> <td> <a id="href1" href="consA-Z.aspx?cons=A" target="_self">A </a> </td> <td> |</td> <td> <a id="A1" href="consA-Z.aspx?cons=B" target="_self">B </a> </td> <td> |</td> <td> <a id="A2" href="consA-Z.aspx?cons=C" target="_self">C </a> </td> <td> |</td> <td> <a id="A3" href="consA-Z.aspx?cons=D" target="_self">D </a> </td> <td> |</td> <td> E</td> <td> |</td> <td> F</td> <td> |</td> <td> G</td> <td> |</td> <td> H</td> <td> |</td> <td> I</td> <td> |</td> <td> J</td> <td> |</td> <td> K</td> <td> |</td> <td> L</td> <td> |</td> <td> M</td> <td> |</td> <td> N</td> <td> |</td> <td> O</td> <td> |</td> <td> P</td> <td> |</td> <td> Q</td> <td> |</td> <td> R</td> <td> |</td> <td> S</td> <td> |</td> <td> T</td> <td> |</td> <td> U</td> <td> |</td> <td> V</td> <td> |</td> <td> W</td> <td> |</td> <td> X</td> <td> |</td> <td> Y</td> <td> |</td> <td> Z</td> <td> |</td> </tr> </table> </ItemTemplate> </asp:FormView> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="consid" DataSourceID="sqldatasource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="consid" Visible="False" /> <asp:TemplateField SortExpression="consid"> <ItemTemplate> <p align="left" style="font-family: Arial; font-size: small;"> <img src="../images/arrow.gif" border="0" width="10" height="11" /> <a href="Consshorttitle.aspx?<%# Eval("consid") %>"> <%# Eval("consname") %> </a> </p> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="sqldatasource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM consindex Order by consname"> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content>--------------------------------------------in this file i am passing the value "A" to (top of the page) "Z" etc... -------------------------------------------2nd Page <%@ Page Language="C#" AutoEventWireup="true" CodeFile="consA-Z.aspx.cs" Inherits="Cons_consA_Z" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="consid" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="consid" Visible="False" /> <asp:TemplateField SortExpression="consid"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> </td> <td align="left" width="75%"> <%# Eval("consname")%> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM consindex WHERE consname LIKE '@cons%'"> <SelectParameters> <asp:QueryStringParameter Name="cons" QueryStringField="cons" Type="int32" /> </SelectParameters> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content>-----------------------------The content which i bold in the 2nd file .. wanted dynamically...... please give me a solution ... -----------------------------I want to take the letters starting with A, B, C to Z dynamically in the Second Page,.... Thanks in advance
View 6 Replies
View Related
Nov 9, 2004
I want to create a report which uses a query that has a dynamic "in" list.
something like
select * from employee where employeeID in (@empid)
when I send @empid = 1,2,3 , it shows a compiler error ,
"syntax error converting the nvarchar value '1,2,3' to a column of data type int'.
any workarounds?
View 3 Replies
View Related
May 15, 2005
Hi,
I have basic design question regarding dynamic query,
When we have to build a dynamic query (which has table name also as an input parameter),
->Is it better to write a stored procedure ..?
or
->directly specify the dynamic query and set the command type as text in .NET code...?
I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..
Please through some light on this,
TIA
View 2 Replies
View Related
Jul 19, 2005
I have an array list of Branch_ID's; i need to select all records from 2 tables that have any of the branch id's in that array associated with them. How would I go about doing this?
View 3 Replies
View Related
Jan 4, 2001
Hi
I have two options two write queries one Static & one Dynamic.
I wanted to know which one will be good.
Example
if @x = 1
Select * From Customers order by CustomerID
Else IF @x = 2
Select * From Customers Order by CustomerName
Else IF @X = 3
Select * From Customers Order by city
else..
...
Against this
Set @Strsql = "Select * From Customers order by " + @Orderby
Exec (@Strsql)
Which will have better performance ?
Is SQL Stores query plans for static queries with all if statements clause
or it remains as good as dynamic ?
Thanks,
Manoj
View 1 Replies
View Related
Oct 19, 2004
I'm the new guy and I could use a hand. Any insight would be appreciated.
Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.
row1 x y z
row2 x b z
row3 x m z
results should look like :: x, (y + b + m) , z
Thank you in advance for any help you can provide!
View 6 Replies
View Related
Jun 20, 2008
Hi
I am new to sql. I have to run a query in a manner that I can see customer name and their photo dynamically? I already have table created where names and customers images are there.Please help.
Thanks
Swati
View 6 Replies
View Related
Oct 7, 2006
month wise production
Format that I want
item codenameJulyAugSepOctNovDecjan
1002pvc resin 3020115060140
3501SWR pipe566045801002020
I create crosstab procedure as follows
create procedure up_CrossTab (@SelectStatement varchar(1000),
@PivotColumn varchar(100),
@Summary varchar(100),
@GroupbyField varchar(100),
@OtherColumns varchar(100) = Null)
AS
/*
Inputs are any 1000 character or less valid SELECT sql statement,
the name of the column to pivot (transform to rows), the instructions to summarize the data, the field you want to group on, and other fields returned as output. 1
*/
set nocount on
set ansi_warnings off
declare @Values varchar(8000);
set @Values = '';
set @OtherColumns= isNull(', ' + @OtherColumns,'')
/*
An 8000 varchar variable called @values is created to hold the [potentially filtered] values in the pivot column. @Values is initiated to an empty string. Then, a temporary table is created to hold each unique value. After the table is created, its rows are loaded into the variable @values. It's usefullness completed, the temporary table is destroyed. 2
*/
create table #temp (Tempfield varchar(100))
insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotColumn + ') as Tempfield FROM (' + @SelectStatement + ') A')
select @Values = @Values + ', ' +
replace(replace(@Summary,'(','(CASE WHEN ' + @PivotColumn + '=''' +
Tempfield + ''' THEN '),')[', ' END) as [' + Tempfield )
from #Temp
order by Tempfield
drop table #Temp
/*
Finally, a dynamic sql select statement is executed which takes the GroupByField, and OtherColumns, passed into the procedure, and each of the Values from the Pivot Column from the passed in SELECT statement . 3
*/
exec ( 'select ' + @GroupbyField + @OtherColumns + @Values +
' from (' + @SelectStatement + ') A GROUP BY ' + @GroupbyField)
set nocount off
set ansi_warnings on
GO
And then my sql query is as like
EXEC up_CrossTab 'SELECT ProdId, GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD
INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ',
'Year(GrnDate)', 'sum(Quantity)[]', 'ProdId'
error occurring
ambiguous column name ‘ProdId’
But when I compile this query
EXEC up_CrossTab 'SELECT grnNo,GrnDate,Quantity FROM inteacc..IcGrnD IcGrnD
INNER JOIN inteacc..IcProduct IcProduct ON (IcGrnD.ProdId=IcProduct.ProdId) ',
'Month(GrnDate)', 'sum(Quantity)[]','GrnNo'
Output
GrnNo12249
1220NullNull20
2Null20Null10
3NullNull300Null
4NullNull10Null
I could not understand the error.
What will I do to get the format I want?
shohan
View 1 Replies
View Related
Jan 30, 2008
how to create dynamic query and use it as a
table. Like the way we use OPENROWSET or
OPENQUERY?
like...
select * from sp_executesql('select * from mytable')
Thanks..
Sandeep.
View 8 Replies
View Related
Sep 17, 2006
Hello
I have a problem with writing a query.Let me give an example:
Table:
ColA , ColB , ColC , Col1 , Col2 , Col3 , Col4 , Col5
Ok.I must write a SP and it gets a parameter,say @param. if @param=1 then in the select statement I will select Col1,if @param=2 then I will select Col2 and so on.
How can I do this?
Thanks.
View 6 Replies
View Related
Jul 19, 2007
Good Day to all,
I just started using SQL Server 2005 and didn't have any experience with other DB...
Is it possible to make a dynamic query? I would like to make a stored procedure where the table name from where it will select/insert/update data is user inputed... if it's possible, can someone please guide me.. thanks in advance...
example:
@TableName VARCHAR(20),
SELECT * FROM @TableName
is it possible?
View 6 Replies
View Related
Feb 28, 2008
Hi
I need some help on a query. I need to delete some records from a table, this table has a dependency to another table
Table 1: dbo.Accounts and Table 2: dbo.AccountsToUser
In the dbo.Accounts table there are
AccountId and OwnedByAccountId
54708002 54708001 54708003 54708001 65708002 65708001 65708003 65708001 54708001 2233440165708001 NULL
In the dbo.AccountsToUser there are
AccountId and UserId
65708002 10065708003 10165708003 10465708003 10654708001 19465708002 199
What I need is to delete every record from dbo.AccountsToUser that has an account connection to an account in the dbo.Accounts that has OwnedByAccount like NULL
So in the example above I should delete from dbo.AccountsToUser
65708002 10065708003 10165708003 10465708003 106
Since they are connected to 65708001 which has OwnedByAccountStatus like NULL
I could delete the records manually since the table is still hand able, but I need this to be a daily job so all help would be very nice
Thanks!
View 6 Replies
View Related
Mar 31, 2008
Can any one please correct this query.Shall i write like this.Please correct this query.
Delete globalDocs.dbo.gdoc_File set IsTrue=1 where FileID='abc'
View 6 Replies
View Related
Apr 11, 2008
In sql server ...is there any opton to delete any particular row..
i have table student
no name
1 raja
2 pravin
3 abraham
suppose i what to delete 2nd row is there any option to delete whithout specifying no or name just only row(ie 2)
pl reply me....
View 6 Replies
View Related
Apr 12, 2008
Hi friends, I want to delete more than record using id.i pass group of id(with string concat) like 10|11|20|25. in stored procedure i want to delete corresponding record.10112025 any idea?Thanks,Durai
View 3 Replies
View Related
Feb 25, 2002
Below are 2 tables and data that I have.
Table1
col1col2col3
--------------------
11aaa
12bbb
13ccc
21ppp
22qqq
23rrr
Table2
col1col2
-------------
11
12
21
Now I want to delete all rows from Table1 which doesn't have a matching entry in Table 2. After the delete, this is what my Table1 data should be:
Table1
col1col2col3
--------------------
11aaa
12bbb
21ppp
Can some one give me a query to accomplish this? Thanks in advance for your help.
View 1 Replies
View Related
Oct 7, 2006
Hi folks
Hoping someone can help me out with my query query!
I'm trying to write a script to do the following: delete everything from table1 where column A and B (of table1) does not match column A and B of table2
Any ideas??
Thanks as ever,
Georgia
View 1 Replies
View Related
Aug 2, 2004
Hi,
Is this a valid SQL Server query :
DELETE FROM D FROM D WHERE LEFT JOIN H ON H.key=D:key WHERE H.key IS NULL
Please advise.
Thanks,
Sam
View 1 Replies
View Related
Aug 2, 2004
Hi,
Is this a valid SQL Server query :
DELETE FROM D FROM D WHERE LEFT JOIN H ON H.key=D.key WHERE H.key IS NULL
Please advise.
Thanks,
Sam
View 2 Replies
View Related
Apr 5, 2007
Hi,
Not sure if this is possible in a delete query that is why I'm posting out of curiosity.
I have a table with one column (int datatype)
WorkItem
1
1
2
1
3
1
----(6 rows)
Can I delete the first record whose workitemid is 1
So that after the delete the resulting dataset in
WorkItemID
1
2
1
3
1
----(5 rows)
Thanks,
yumyum113
View 8 Replies
View Related
May 25, 2007
hi all,
how do i perform this query?
DELETE FROM tblStkAdjDetail
WHERE (SELECT ItemStorageID FROM tblStkAdjDetail WHERE Status='NEW'
AND ItemStorageID NOT IN (SELECT ItemStorageId FROM tblTempTableForRecvPacking) )
~~~Focus on problem, not solution ¯(º_o)/¯ ~~~
View 10 Replies
View Related
Feb 21, 2008
Hi All
I am beginner ot SQL Server.
I want to know that when we delete a row or a set of Rows from a table, it'll only make the space available for subsequent inserts into it or will the Delete also free the memory used by the table.
Suppose I am inserting customer records in the details table when the customer comes into the system. If i make a logic to delete the customer record from the table and insert into the backup table when it leaves the system(As the data inserted is quite large and my application queries into this table at each transaction). Will it help in optimizing the SQL Queries or it is useless to do so.
Thanks
Harsh Dhawan
Harsh Dhawan
View 4 Replies
View Related
Aug 7, 2007
I have part of a stored proc that I need help with.
I need to figure out how to delete infro from the
tblManifest using the @ssnDelete variable.
I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc.
------------------------------
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------------------
Here is the code fro part of the proceedure.
Thanks in advance.
Gene
----------------------------------------------------------
CREATE TABLE dbo.tblTempSSN(
ssn varchar(11) null);
OPEN SYMMETRIC KEY SymKeySSN
DECRYPTION BY CERTIFICATE CertSSN;
INSERT INTO tblTempSSN(ssn)
SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN
FROM tblSoldier s, tblManifest m
WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn
CLOSE SYMMETRIC KEY SymKeySSN;
declare @ssnDelete varchar(11);
set @ssnDelete = (select (m.ssn) as ssn
from tblManifest m, tblTempSSN t
where t.ssn = m.ssn);
delete
from tblManifest
where ssn = '@ssnDelete';
DROP TABLE dbo.tblTempSSN;
----------------------------------------------------------
View 2 Replies
View Related
Aug 7, 2007
I have part of a stored proc that I need help with.
I need to figure out how to delete infro from the
tblManifest using the @ssnDelete variable.
I Have multiple records ffor the given above variable.
Would this need to loop in order to work and that is the solution for this.
I get ther following error when I run the full stored proc.
------------------------------
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------------------
Here is the code fro part of the proceedure.
Thanks in advance.
Gene
----------------------------------------------------------
CREATE TABLE dbo.tblTempSSN(
ssn varchar(11) null);
OPEN SYMMETRIC KEY SymKeySSN
DECRYPTION BY CERTIFICATE CertSSN;
INSERT INTO tblTempSSN(ssn)
SELECT CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) AS SSN
FROM tblSoldier s, tblManifest m
WHERE CONVERT(VARCHAR(11), DecryptByKey(s.SSN)) = m.ssn
CLOSE SYMMETRIC KEY SymKeySSN;
declare @ssnDelete varchar(11);
set @ssnDelete = (select (m.ssn) as ssn
from tblManifest m, tblTempSSN t
where t.ssn = m.ssn);
delete
from tblManifest
where ssn = '@ssnDelete';
DROP TABLE dbo.tblTempSSN;
----------------------------------------------------------
View 4 Replies
View Related
Jul 4, 2007
I am so sorry if I posted this to the wrong forum, here is my dilemma and I am hoping someone can point me.I have a SQL query such as this... SELECT tblNode, tblCorp, tblService FROM tblName WHERE tblNode = @tblNode AND tblCorp = @tblCorp AND tblService = @tblServiceThe @tblNode, @tblCorp, @tblService are all pulling from 3 separate drop down controls. Ok easy enough.However, I want the drop downs to default to "ALL" and I want all records pulled. Or if only tblCorp and tblNode is done, I want all tblServices.Does this make sense? How can I do this with 1 single SQL String without having to build 27 separate queries and a case statement to get the same result?I guess I am looking to see if a wild card can be used so tblNode = % or something. The server is MSSQLThank you
View 4 Replies
View Related
