In a SP I need to know if certain records already exist
but the query is parameter dependent so I can't code
IF EXISTS (SELECT ...)
because the proper select must be calculated.
Using
EXEC (@CalculatedQuery)
IF @@ROWCOUNT = 0
Puts the results of @CalculatedQuery into my SP result set.
This is highly undesireable.
Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########
View 2 Replies View RelatedI am having trouble with what will surely be a simple query for you experts.I have 2 tables with inventory data.IMITMIDX contains the master item infoIMINVLOC contains location specific data such as quantity on hand at thatlocation.These tables have 2 commons fields, ITEM_NO and LOCI need to search the IMINVLOC table for any records where ITEM_NO and LOC donot match that in the IMITMIDX table.The following query give me zero records even though I can manually findsome records:SELECT *FROM IMINVLOC_SQL INNER JOINIMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_nowhere not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =IMINVLOC_SQL.loc)Any ideas?Thanks.
View 5 Replies View RelatedI have a NOT EXISTS Query that works, but I have been giving a new requirement from MGMT. They initially wanted to know if a record existed in the system. Now they want to know if one exists over certain time periods (i.e. last 24 hours, 48 hours, etc...)
The System:
This is an ASP.NET application that uses table1 and table2 as list items in a drop down list. Once a record is created in the system it puts a time date stamp on the record along with other fields. For reporting I only care about reporting on two fields. This will be used to show activity in the system. MGMT expects at bare minimum 3 entries per 24 hour period.
SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2))
The above returns records where there are no entries in Table3 for table1.Department AND Table2.field2. I now have the requirement to do the same thing, but also to include records where table3.insert_date was in the last 24 hours, 48 hours, etc...
I have tried adding:
Table3.insert_date >= GetDate() - 1)
but it still only returns records with no entry at all.
SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2 AND Table3.insert_date >= GetDate() - 1))
I have moved it all over the place in this query changed AND to OR and cannot get the desired result.
Hello,
i hope that somebody can help me. I have a problem with ms sql query.
i have two tabels(wtmenSends and T1) in a database. i need pick up all email addresses from table wtmenSends but without those email addresses which are in table T1.
I have written this sql query to get these Emails, but i get nothing by this query. Can somebody tell me, where is the problem? Thanks.
select wtmenSends.Email
from wtmenSends
where not exists(
select T1.*
from(
SELECT wtmenSends.Id, wtmenSends.Email, wtmenSends.IDMailing, wtmenSends.Title, wtmenSends.Firstname, wtmenSends.Lastname, wtmenSends.IDUser,
wtmenSends.IdStatus, wtmenSends.IsSent, wtmenSends.DateSent, wtmenSends.wtobjIDClass, wtmenSends.wtobjDateCreated,
wtmenSends.wtobjDateChanged, wtmenSends.wtobjUserCreated, wtmenSends.wtobjUserChanged
FROM wtmenRobinsons INNER JOIN
wtmenSends ON wtmenSends.Email NOT LIKE '%' + wtmenRobinsons.Filter AND wtmenRobinsons.IsDomain = 1) as T1 inner join
wtmenRobinsons on wtmenRobinsons.Filter = T1.Email)
I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan
I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins.
My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against.
Can I nest Exists in a Where clause?
If so how is the 2nd exists statement added to the query?
see my sample query below
================================================== ===
'With 2 tables
SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
ORDER BY SITE
'with 3 tables this doesn't work
SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
AND
(SELECT *
FROM BIG_TBL b1
WHERE d1.fqdn = b1.xyz)
ORDER BY SITE
================================================== ===
Thanks for the help
Jim
I've been presented with a task to do a query similar to the followingand I was curious as to what the quickest query would look like.Anyone have any ideas??Some_Id Value1 A1 B1 C2 C2 A2 B3 B3 C4 C5 Q5 C5 R6 T7 P7 BThe problem is that I want to select one record for each ID. If arecord with the value of 'A' exists, then I want to select that recordfor that ID. If not, I want to select the record with the value 'B'for that ID if it exists. Otherwise, just give me the first record forthat ID that exists. The result set would look like this:Some_ID Value1 A2 A3 B4 C5 Q6 T7 BThanks for your input!
View 12 Replies View RelatedI am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?
Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int
Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'
[Code] .....
I want to only go after Distinct email addressess that contain an @ symbol.
But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?IF EXISTS (SELECT DISTINCT user_username FROM usr WHERE(user_username LIKE N'%@%'))
SELECT *
FROM usr Order By user_username
I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.
There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:
Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]
From gridhdr pg inner join customers c on c.grid_id = pg.grid_id
Order by pg.grid_id
When i am running below snippet execution plan is showing constant scan instead of referring subquery table.
I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.
select count(*) from A where exists (select count(1) from B where A.a=B.a)
execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.
So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...
select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,
[Code] ......
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
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
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!
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?
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
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?
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
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 RelatedHi
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
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!
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
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
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.
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.
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?
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 RelatedHii I am Varun i have a problem with the dynamic stored procedure
This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1
(@TeacherID_OnLeave numeric(9),
@DateFrom datetime ,@DateTo datetime , @UserID numeric(9)
)
AS
declare @flag as numeric(9)
declare @year as varchar(4)
set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0)
if( @year=null or len(@year)=0)
set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+')
begin
insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+');
end')
else
set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave)
return @flag
--exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'
--')
--return @flag
--exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
I created procedure to execute dynamic query.
if my query is Q1='select empno from EMP;' it works fine but it does not work with query Q2='select empno into @v_empno from EMP where empno = :xemp';
I am using
exec sp_excutesql @Q2, @xemp =21;
Can we not use INTO in dynamic query....?
it gives me syntex errors for @v_empno ...???
Any help appriciated...
Good afternoon,
i'm new to Functions on the SQL server
I'm trying to create a dynamic query that would select the the column passed to the function from a certain table
my table called selected_Date, and has StartDate, and EndDate columns
when the user select for example "StartDate", i pass this as a variable to the function which runs the query. but i always gets back the passed string as a result..
here is my table
StartDate | EndDate
---------------------
20071231 | 20080306
here is my function knowing that i'm passing "Start" as a variable:
Code:
ALTER FUNCTION tu_efgn_int.Get_StartEndDates(@DateType varchar(10))
RETURNS varchar(8)
AS
BEGIN
DECLARE @vDate varchar(15)
Set @vDate= (Select @DateType + 'Date' From dbo.ps_tbl_SelectedDates)
RETURN @vDate
END
@vDate always returns "StartDate" as result instead of 20071231 why is that ?
I have a sproc that runs as a job every day. Since the first of the year, it hasn't been running properly (it errors out). It builds an SQL statement dynamically, and then executes it.
If I try to run it with QA, I get the following message:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FCST'.
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, ...
However, if I just copy the entire Update statement contained in the error message into the QA window, and execute it, it runs just fine.
What could I be missing?
UPDATE OPSPLAN SET Jan_07=Jan_fcst, Feb_07=Feb_fcst, Mar_07=Mar_fcst,
Apr_07=Apr_fcst, May_07=May_fcst, Jun_07=Jun_fcst, Jul_07=Jul_fcst,
Aug_07=Aug_fcst, Sep_07=Sep_fcst, Oct_07=Oct_fcst, Nov_07=Nov_fcst,
Dec_07=Dec_fcst
FROM (SELECT [YEAR], PLAN_SHIP.BOD_INDEX, BOD_HEADER.PRODUCT,
Jan_fcst, Feb_fcst, Mar_fcst, Apr_fcst, May_fcst, Jun_fcst, Jul_fcst,
Aug_fcst, Sep_fcst, Oct_fcst, Nov_fcst, Dec_fcst
FROM PLAN_SHIP INNER JOIN BOD_HEADER
ON PLAN_SHIP.BOD_INDEX = BOD_HEADER.BOD_INDEX
WHERE (SCEN_ID = 1) AND ([Year] = 2007)
) PS INNER JOIN OPSPLAN ON PS.BOD_INDEX = OPSPLAN.BOD_INDEX
WHERE OPSPLAN.SRCPLAN = 'SHIP'