Passing Vars To An In Statment

May 9, 2008

i am going mad. when i hardcode my usernames into my in statment i get the results i expect...

Code------------------------------
Select u.UserID from dbo.[User] u where u.UserName in ('iresponsejamie.seaton','iresponsephil.smith)


Result---------------------------
3509
3506




but when i use a variable with more than one user in it and pass that into my in clause i get nothing returned when i should have the same result set.

Code------------------------------
DECLARE @CollectorList VarChar(100)

Set @CollectorList= '''iresponsejamie.seaton'',''iresponsephil.smith'''

Select u.UserID from dbo.[User] u where u.UserName in (@CollectorList)

Result---------------------------
nothing


as i said if i use - Set @CollectorList= 'iresponsejamie.seaton' - it works fine but when i include another user - Set @CollectorList= '''iresponsejamie.seaton'',''iresponsephil.smith''' - i get nothing back.

any ideas guys

View 7 Replies


ADVERTISEMENT

User Vars Of Type String Initialized To Nulls Not Passing Correctly To Sp

Feb 24, 2008

i defined 3 pkg scope user variables of type string in the ssis variables window, typed null as their value, and tried passing them (thru exec sql task) to an sp who expects 3 varchar(23) params.

The sp is blowing up because their values arent null.

The sql task command reads exec sp_name ?,?,?. In the sql task editor's param mapping window, I have each param listed with direction "input", data type varchar and the individual sp params in the parameter name column. I think the plumbing is set up correctly because I'm fine when I send the System Variable StartTime as a 4th param to the sp with data type DATE on the ssis end and datetime on the sp end.

Does anyone know if ssis string and engine varchar are incompatible or perhaps if null in the variables window doesnt initialize variables?

View 1 Replies View Related

Passing A Querystring To A Insert Statment.

Feb 1, 2008

Hi, I'm having problems passing a querystring from the datanavigateurlformatstring to be used in a insert sp on a webform.  Please can someone take a look at my code and point me in the right direction.   At the moment I'm gettingthis error messageCompiler Error Message: BC30451: Name 'userid' is not declared.  but in the url i can see the querystring I've passed from the previous page.
Thanks in advance Dave
 1 Protected Sub Execute_Clicked(ByVal sender As Object, ByVal e As EventArgs)
2
3 Dim objConn As New System.Data.SqlClient.SqlConnection()
4 objConn.ConnectionString = "My connection string"
5 objConn.Open()
6 Dim objCmd As New System.Data.SqlClient.SqlCommand("test_insert", objConn)
7 objCmd.CommandType = System.Data.CommandType.StoredProcedure
8
9
10 Dim puserid As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@userid", System.Data.SqlDbType.Int)
11 Dim pdate As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@date", System.Data.SqlDbType.DateTime)
12 Dim papplicants As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@applicants", System.Data.SqlDbType.Int)
13 Dim pclients As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@clients", System.Data.SqlDbType.Int)
14 Dim pcontacts As System.Data.SqlClient.SqlParameter = objCmd.Parameters.Add("@contacts", System.Data.SqlDbType.Int)
15
16
17
18
19
20 puserid.Direction = System.Data.ParameterDirection.Input
21 puserid.Value = Convert.ToInt32(userid.QueryString)
22 pdate.Direction = System.Data.ParameterDirection.Input
23 pdate.Value = Convert.ToDateTime([date].Text)
24 papplicants.Direction = System.Data.ParameterDirection.Input
25 papplicants.Value = Convert.ToInt16(applicants.Text)
26 pclients.Direction = System.Data.ParameterDirection.Input
27 pclients.Value = Convert.ToInt16(clients.Text)
28 pcontacts.Direction = System.Data.ParameterDirection.Input
29 pcontacts.Value = Convert.ToInt16(contacts.Text)
30
31
32
33 command.ExecuteNonQuery()
34
35
36 objConn.Close()
37
38 End Sub
 

View 7 Replies View Related

Insert And Delete Statment In One Statment

Jan 18, 2008



Hi all

I have two tables I need to Select the record from the First table and insert them into the second table and delete the record from the first table how can i do that with the SQL Statment?

Thank you in advance .....

Regards,
sms

View 15 Replies View Related

Vars In From Clause

Oct 12, 1999

Hi!

I've a dude about the "from" clause. I want execute a query where the from clause has variables wich repesent table names. Example:

select <..,..,>
from @var1, @var2

Can I do that? How?

I hope your help. Thanks in advance.

Francisco Castillo.

View 1 Replies View Related

Set Vars In Stored Proc To First And Last Day Of Year

Aug 8, 2006

Hello,

Nice simple question from someone making there way with their first stored prcoedure. I am editing a procedure where I have hardcoded the two date variables to 01/01/06 and 31/12/06.

How could I make this year sensitive so that next year the variables are set to 01/01/07 and 31/12/07 automatically.

Many Thanks
Neal

View 5 Replies View Related

Dynamic Stored Procedures Uses Vars Only

Oct 7, 2006

Hi there,

I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.

And also how to Dynamicly create a sp_GetNameByID (for instance)

using vars only.

Thanks

It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.

View 1 Replies View Related

How To Sum A Column Depending Of Another Colum Into Different Vars?

May 20, 2006

Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example

DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)

SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount

What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3

Thanks for the help


@puy

View 5 Replies View Related

Best Way To Set User Vars Based On Query

Mar 1, 2008

what's the best way to set user vars in ssis from a query. I have some components that set a from and to date in a table. I'd like to select those values right into some user vars in the pkg.

View 1 Replies View Related

Declaring And Setting Vars In A Stored Procedure

Feb 2, 2008

Hello,

I have a stored procedure that prompts the user for a year and
a month. Based on the month selected, I need to determine
the number of days in that month.

I have tried declaring variables to use to calculate number
of days in month and a counter, but they are not
working. When I try to run this it says I have to prompt user for
these as well. How do I declare and set a variable inside
a stored procedure.

Sample of my code is below...

REATE PROCEDURE crm_contact_frequency_report

@TheYear varchar(4),
@TheMonth integer,

@MyCount integer,
@NumDays integer


AS









SELECT

/* EMAILS (B) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 1)
) AS Total_EmailOutgoing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 0)
) AS Total_EmailImconing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode IS NULL)
) AS Total_EmailNotListed,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
) AS Total_All_Emails,


/* PHONE CALLS (C) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 1)
) AS Total_CallOutgoing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 0)
) AS Total_CallIncoming,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode IS NULL)
) AS Total_CallNotListed,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
) AS Total_All_Calls,

/* FAXES (D) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 1)
) AS Total_FaxOutgoing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 0)
) AS Total_FaxIncoming,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode IS NULL)
) AS Total_FaxNotListed,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
) AS Total_All_Faxes

FROM CampaignResponse A
GO

View 2 Replies View Related

Asssigning Values To Multiple Vars In A SP In One Go (without Temp Table)

Feb 22, 2007

I have to select several field values from a table and need to assign them to different variables in my SP.Here's what I do now:
declare @ReceiverEmail nvarchar(50)
SET @ReceiverEmail=(SELECT Email FROM Users WHERE UserCode=@UserCodeOwner)
declare @UsernameSender nvarchar(50)
SET @UsernameSender=(SELECT Username FROM Users WHERE UserCode=@UserCodeOwner)As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:SSo, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table....)?

View 4 Replies View Related

Select Record Based On Multiple Criteria (vars)

Apr 17, 2008

Hi! I'm new to SQL and have a question...

I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.

Basically my SELECT statement looks something like this, then is followed by an If... Else statement


SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress

If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If

RecordSet.Update
RecordSet.Close


Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??

View 5 Replies View Related

Help With Sql Statment?

Jul 11, 2006

I cant find the error in this sql statment. What I want it to do is return everything from book, locations.locationname, and author.fullname. I want to to only return the first 10 rows. This will be used in paging, so, eventually it will be first 10, than 11-20, etc. Heres what I have, without the row limitSELECT RowNum, book.*, locations.LocationName, author.fullname FROM (SELECT book.*, locations.LocationName, author.fullname ROW_NUMBER() OVER(ORDER BY book.id) as RowNum FROM book INNER JOIN Author ON book.AuthorID = Author.ID OR book.AuthorID2 = Author.ID OR book.AuthorID3 = Author.ID OR book.AuthorID4 = Author.ID OR book.AuthorID5 = Author.ID INNER JOIN Locations ON book.LocationID = Locations.ID WHERE (Author.FullName LIKE '%' + @Search + '%') OR  (Author.FirstName LIKE '%' + @Search + '%') OR (Author.LastName LIKE '%' + @Search + '%')) as BookInfo

View 6 Replies View Related

SQL Not Statment Help

Jul 11, 2007

I have two tables. One for videos and one for a "block list"Videos : VideoID UserID VideoURL VideoTitle etc. VideoBlockList :VideoID UserIDI have a datalist to show the videos but i want for the datalist to miss out any videos that a user can not see.So if the block list has "VideoID" = 2 and UserID = 1 if user 1 does a search then the search will skip out the video 2.how is this done? i tryed to do it using a specific / custom SQL statment but it errored cos the NOT value conflicted with the search... any ideas? thanks in advance si! 

View 19 Replies View Related

Sql Statment

Nov 13, 2007

Select @ID  = top 1 ID From Contents Where Contents.UserID=@UserID And Contents.Status=4  AND Contents.InEdit=1
why it dosn't get back the ID vlaue but when i remove the top1 one its work well why
 

View 5 Replies View Related

Need Help With Sql Statment

Apr 14, 2008

Hi I have two tablesTABLE 1 named problemas with the field N_problem (numeric)Tabe 2 named  resolvidos with the field Resol (numeric)
How can i select all the records from table 1 who are not in Tabe2 ?
Thank you
mario

View 2 Replies View Related

IF Statment

May 16, 2002

Does anyone know how to write a statement in SQL Server that is similiar to Microsoft Access's IIF function. Im not quite sure how the syntax works in a SQL Server IF statement. Thanks!

View 1 Replies View Related

If Statment

Apr 2, 2007

need help with if statment in a stored procedure
here is what i have and it does not work

Code:


CREATE PROCEDURE Get_ckcompany
@cknum int,
@company varchar

AS

if (@cknum is not null) and (@company is null)
select *
from PMTK_tbl
where Company = @company

else if (@compnay is not null) and (cknum is null)
select *
from PMTK_tbl
where check_Num = @cknum
else
select *
from PMTK_tbl
where Check_Num = @cknum and Company = @company
end if

GO

View 2 Replies View Related

Need Help With An Sql Statment

May 1, 2008

I have a database used for a point of sale system
it has a main table with the total amount of a check - the tip
the tip is save in a different table that holds payments the issue is i need to make a statement that finds checks based of the total with the tips added. the table that holds tips can have more then one tip because you can have multiple payments on a check.

The statement i have now is like this

select jc.total + sum(jp.tip) as total from jc innerjoin jp on key
where total <= @max and total >= @min
group by jc.total

but this statement uses total before the tip is added. I am not sure how else to do this any help would be great

View 4 Replies View Related

SQL Statment

Jun 1, 2006

select zsong.song, zsong.trk
from zsong, zfmt
where zfmt.upc='13117' AND zfmt.muzenbr=zsong.muzenbr


That staement works, but if the UPC code is repeated twice it returns the results twice (i want to only read it once)...

how do i make it only return the first result or the last one or whatever (since they all reference the same thing)

View 4 Replies View Related

Use Statment

Jun 9, 2008

Posted - 06/09/2008 : 11:10:47
--------------------------------------------------------------------------------

trying to run the following script.

use 001
select *
from imitmidx_sql

Get incorrect syntax near 001

If I change the use 001 to Data_58 it works fine. Do I need special syntax when the database name is 001??

View 1 Replies View Related

SQL Statment

Feb 18, 2007

Hello All,

I imported a excel file from SSIS and created a table called Lockbox.
To avoid the user from having to change the excel file -it is being imported as is.
I only need 4 fields: [Contract ID] , [Check Number], [Owner ID], [Site ID]

The table I need to import to Transaction has Diffrent Column Names -ex-CustomerID, ResortID.
The columns are in diffrent order.
And I need to add more information into them like UserID = 'Hwells', Trantype = 'MF'
and convert to a diffrent data type [Site ID] to text.

Is their a sql statment that can do this?

SQL2005

Thanks for your time

View 2 Replies View Related

Sql Statment

Aug 17, 2007

hi,

I Have a following SP running on sql 2005:-

DECLARE @MONTHNO int
DECLARE @DB CHAR (8)
DECLARE @CR CHAR (8)
SET @MONTHNO = 2
SET @DB = 'DB_'+CONVERT(NCHAR(2),@MONTHNO)
SET @CR = 'CR_'+CONVERT(NCHAR(2),@MONTHNO)
SELECT (acctno),@CR from bmaster

. The table has a 12 field with cr_1 ... to 12

. the sql can’t understand the @cr the column papers "with No column name'

View 2 Replies View Related

SQL Statment By Asp.net

Oct 8, 2006

is there any chance to use the follwoing script to add new record to the database

"

rs.addnew

rs.("name")="Scot"

rs.update

"

insted of the old fashion " insert into ...."





View 1 Replies View Related

SELECT Statment

Mar 20, 2007

Hello, Is there an SELECT statement to just return the last 100 row in my tables?  I have about 500 rows in my tables and I only need the info on the last 100 rows.
 Thanks
Steve

View 4 Replies View Related

A SELECT Statment, Please Help Me

Oct 2, 2007

hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS

View 5 Replies View Related

Optimize SQL Statment

Mar 9, 2008

Hello, I have three sql select statments I would like to combine into one.  I have created a statment that works but I am not sure if it is a good solution performance wise.  Is there a better way to run this query?
Thanks Very Much!!
 if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate)
Begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate
End
else
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc)
begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc
end
else
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Order By nextupdate 

View 1 Replies View Related

Sql Insert Statment

Mar 28, 2008

i have 3 tables  in sql the relation between them is one to one
person ==> employee ==> sales_department_stuff&
another relation ( one to one )between
person(the same as above ) ==> customer 
i want to put person id in employee not repeated in customer becase all (employee and customer is a person ) 
sooooooo
i put this sql statment to try to insert person id in employee then in sales_department_stuff  table to complet his ordersinsert into Person (Person_Name_Ar,Person_Name_En) values ('aaaaa',' ssssssss')select @@identity from Personinsert into Employee values (@@identity,'1') select @@identity from Personinsert into sales_department_Staff values (@@identity,'1')select @@identity from Personinsert into Driver  values (@@identity,'2','2222','1/1/2005','5') /* SET NOCOUNT ON */
      RETURN
 
 the first 3 statment is run and success
but underline stetment return error that
he can insert null value in id field ,,,,,,,,,,,,,,,,
he can compile @@identity in these statment ??????????????
plz help me
 
  
 

View 7 Replies View Related

Using Date In SQL Statment

Jun 3, 2006

Hello All;
I am using an sql statement to pass only the year using DatePart function, but i receive the following error.

curYear = DatePart(DateInterval.Year, Now)
Dim Date1 = "'" & curYear & "/01/01'"
Dim Date2 = "'" & curYear & "/31/12'"
Dim strAll As String = "SELECT * FROM Boxes WHERE EntryDate BETWEEN Convert(DateTime, " & Date1 & ") And CONVERT(DATETIME," & Date2 & ")"
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
What is the cause, please?

View 2 Replies View Related

Help On Sql Select Statment

Apr 20, 2007

Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null....
IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.

Code:


Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc,
t2.site_id,t3.CompanyCode,t3.CostCenter
From( PMTK_tbl as t1
Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num)
inner Join CostCenters AS t3
on t2.market = t3.market and t2.market_region = t3.RegionCode

View 4 Replies View Related

Using Case Statment

Apr 7, 2008

Hi There i have a query that uses conditional Select statment my query is:
Select A,B,C case
when '1' then 'one'
when '2' then 'two'
end
from table

what if i want to include addional condition in the case condition like C case
when '1' and A is null then 'C is one and A is null'

Thank you :D

View 3 Replies View Related

Help With Select Statment

May 23, 2008

whats wrong with this statment?

declare @alpha as nvarchar(50)
set @alpha = select top 1 monthyear from dbo.Performance_Indicators_Rolling order by recordkey

I get an error

Incorrect syntax near the keyword 'select'.

Can anyone please help?

View 3 Replies View Related

Sql Statment History

Mar 1, 2006

I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.


dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.

View 2 Replies View Related







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