sql server - How can I include a Temp Table in a SQL Function -

-

i have sql server 2014 server linked oracle server. want use temp table in function return dataset oracle database , use function return results using regular t-sql. since rather new close getting error message

msg 156, level 15, state 1, procedure getbond, line 37
incorrect syntax near keyword 'begin'.

i have posted function code here:

set ansi_nulls on go set quoted_identifier on go  alter function [dbo].[getbond]     (@warcontrolid bigint) returns varchar(max) begin     --create temp table     declare @tsql varchar(max)      declare @warrantbail table      (          wr_invl varchar(5),          wr_warr_ctl varchar(10),          wr_bail varchar(50),          wc_bail varchar(50)     )      select  @tsql = 'select * openquery(rmsprod2,''select tiburon.wrmast.wr_invl, tiburon.wrmast.wr_warr_ctl,tiburon.wrmast.wr_bail,tiburon.wrwchg.wc_bail                      tiburon.wrmast                       left join tiburon.wrwchg on wrwchg.wc_wr_chain = wrmast.wrmast_row                      tiburon.wrmast.wr_warr_ctl = ''''' + @warcontrolid + ''''''')'      insert @warrantbail        exec (@tsql) end  begin         -- create variable     declare @nobailcount int     declare @chgcount int     declare @wartotalbond float     declare @chgtotalbond float     declare @war_final_bail varchar(max)      select count(distinct w.wr_bail) nobond_count         @warrantbail w         w.wc_bail in ('no bond', 'no bail','none') or w.wr_bail in ('no bond', 'no bail','none')  --***********get charge count         select count(w.wc_bail) chgcount @warrantbail w          --******************if above fails have bond check warrant bond amount         select sum (distinct cast(w.wr_bail int)) war_bond_total                 @warrantbail w                 w.wr_bail not in ('no bond', 'no bail','none')  --****************we may have additional charges total charges         select sum (cast(w.wc_bail int)) chg_bondtotal                 @warrantbail w                 w.wc_bail not in ('no bond', 'no bail','none')      if (@nobailcount > 0)         begin              set @war_final_bail = 'no bail'         end     else if @chgcount > 0         begin                set @war_final_bail = @wartotalbond + @chgtotalbond         end     else         begin         set @war_final_bail = @wartotalbond         end     return convert(varchar(max), @war_final_bail)  end

in addition error when execute code seeing squiggly line under line "alter function [dbo].[getbond]

that error states:

incorrect syntax: 'alter function' must statement in batch.

does error mean cannot create temp table in function?

why have end , begin here? think (one of) problem(s).

insert @warrantbail        exec (@tsql) end  begin         -- create variable     declare @nobailcount int

do absolutely have use dynamic sql? why not this...

insert @warrantbail select *  openquery(rmsprod2, '     select tiburon.wrmast.wr_invl,       tiburon.wrmast.wr_warr_ctl,     tiburon.wrmast.wr_bail,     tiburon.wrwchg.wc_bail     tiburon.wrmast          left join tiburon.wrwchg on wrwchg.wc_wr_chain = wrmast.wrmast_row     tiburon.wrmast.wr_warr_ctl = ' + cast(@warcontrolid varchar(30)) + ')')

Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more